350 lines
17 KiB
C#
350 lines
17 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Text;
|
|
using System.Threading;
|
|
using System.Windows.Forms;
|
|
|
|
namespace CommoditySaleDataFrom
|
|
{
|
|
public partial class MainForm : Form
|
|
{
|
|
static string strServer = ConfigurationManager.AppSettings["Server"].ToString();
|
|
static string strPort = ConfigurationManager.AppSettings["Port"].ToString();
|
|
static string strName = ConfigurationManager.AppSettings["Name"].ToString();
|
|
static string strUserName = ConfigurationManager.AppSettings["UserName"].ToString();
|
|
static string strUserPwd = ConfigurationManager.AppSettings["UserPwd"].ToString();
|
|
|
|
static string strServer_CM = ConfigurationManager.AppSettings["Server_CM"].ToString();
|
|
static string strPort_CM = ConfigurationManager.AppSettings["Port_CM"].ToString();
|
|
static string strName_CM = ConfigurationManager.AppSettings["Name_CM"].ToString();
|
|
static string strUserName_CM = ConfigurationManager.AppSettings["UserName_CM"].ToString();
|
|
static string strUserPwd_CM = ConfigurationManager.AppSettings["UserPwd_CM"].ToString();
|
|
|
|
static AppCode.OracleHelper _OracleHelper = new AppCode.OracleHelper(strServer, strPort, strName, strUserName, strUserPwd);
|
|
|
|
static AppCode.OracleHelper _OracleHelper_CM = new AppCode.OracleHelper(strServer_CM, strPort_CM, strName_CM, strUserName_CM, strUserPwd_CM);
|
|
|
|
static string strPROVINCE_CODES = ConfigurationManager.AppSettings["PROVINCE_CODES"].ToString();//配置省份
|
|
static int n_Hour = Convert.ToInt32(ConfigurationManager.AppSettings["Hour"].ToString());//定义生成
|
|
|
|
private bool b_False = false;
|
|
|
|
Thread thread = null;
|
|
public MainForm()
|
|
{
|
|
InitializeComponent();
|
|
}
|
|
|
|
private void MainForm_Load(object sender, EventArgs e)
|
|
{
|
|
this.dtpStarTime.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
|
|
dtEndTime.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
|
|
dtStarTime.Text = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd");
|
|
}
|
|
|
|
|
|
private void BtnStar_Click(object sender, EventArgs e)
|
|
{
|
|
b_False = b_False ? false : true;
|
|
BtnStar.Text = b_False ? "停止" : "开始";
|
|
if (lvMessage.Items.Count > 5000)
|
|
{
|
|
lvMessage.Items.Clear();
|
|
}
|
|
if (b_False)
|
|
{
|
|
try
|
|
{
|
|
ParameterizedThreadStart threadStart = new ParameterizedThreadStart(StarDate);
|
|
thread = new Thread(threadStart);
|
|
thread.Start();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (thread != null)
|
|
{
|
|
try
|
|
{
|
|
thread.Abort();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
private void StarDate(object obj)
|
|
{
|
|
while (true)
|
|
{
|
|
this.Invoke(new Action(() => { this.dtpStarTime.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"); this.lbMessage.Text = ""; }));
|
|
|
|
|
|
if (DateTime.Now.Hour == 1 && !b_False)
|
|
{
|
|
b_False = true;
|
|
}
|
|
if (DateTime.Now.Hour == n_Hour && b_False)
|
|
{
|
|
BeginMPSEPARATE(obj);
|
|
b_False = false;
|
|
}
|
|
Thread.Sleep(60000);
|
|
}
|
|
}
|
|
|
|
#region 方法 -> 生成数据
|
|
private void BeginMPSEPARATE(object obj)
|
|
{
|
|
string strTime = this.dtpStarTime.Value.ToString("yyyy-MM-dd");
|
|
try
|
|
{
|
|
this.Invoke(new Action(() => { this.lbMessage.Text = ""; }));
|
|
if (lvMessage.Items.Count > 5000)
|
|
{
|
|
lvMessage.Items.Clear();
|
|
}
|
|
string sql = string.Format(@"SELECT
|
|
A.SERVERPART_ID,A.SERVERPARTSHOP_ID,A.PROVINCE_CODE,A.SERVERPART_NAME,A.SHOPNAME
|
|
FROM
|
|
MOBILESERVICE_PLATFORM.T_SERVERPARTSHOP A
|
|
WHERE
|
|
A.ISVALID = 1 AND A.BUSINESS_STATE = 1000 AND
|
|
','||A.STATISTICS_TYPE||',' like '%,3020,%' AND PROVINCE_CODE IN ({0})
|
|
GROUP BY
|
|
A.SERVERPART_ID,A.SERVERPARTSHOP_ID,A.PROVINCE_CODE,A.SERVERPART_NAME,A.SHOPNAME", strPROVINCE_CODES);
|
|
|
|
List<string> _slist = new List<string>();
|
|
foreach (DataRow item in _OracleHelper.ExcuteSqlGetDataSet(sql).Tables[0].Rows)
|
|
{
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "正在删除" +
|
|
item["SERVERPART_NAME"].ToString() + "-" + item["SHOPNAME"].ToString() + "" + strTime + "号数据");
|
|
}));
|
|
_OracleHelper_CM.ExcuteSql(string.Format(@"DELETE FROM COOP_MERCHANT.T_COMMODITYSALE
|
|
WHERE STARTDATE >= TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') AND
|
|
STARTDATE < TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') + 1 AND PROVINCE_CODE = {1} AND
|
|
SERVERPARTSHOP_ID = {2} AND SERVERPART_ID = {3}", strTime,
|
|
item["PROVINCE_CODE"].ToString(), item["SERVERPART_ID"].ToString(), item["SERVERPARTSHOP_ID"].ToString()));
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add("删除" + item["SERVERPART_NAME"].ToString() + "-" +
|
|
item["SHOPNAME"].ToString() + strTime + "号数据成功");
|
|
}));
|
|
|
|
string strUrl = GetUrl(item["PROVINCE_CODE"].ToString());
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendFormat("?action_type={0}", "GetCommoditySaleData");
|
|
sb.AppendFormat("&serverPartId={0}", item["SERVERPART_ID"].ToString());
|
|
sb.AppendFormat("&serverPartshopId={0}", item["SERVERPARTSHOP_ID"].ToString());
|
|
sb.AppendFormat("&startDate={0}", strTime);
|
|
sb.AppendFormat("&endDate={0}", strTime);
|
|
string parameters = sb.ToString();
|
|
|
|
string strHtml = AppCode.Common.HttpUrlPost(parameters.Replace("?", ""), strUrl + parameters);
|
|
|
|
var data = QRWL.Common.JsonHelper.GetData2JArray(strHtml, "list");
|
|
DataTable _DataTable = new DataTable();
|
|
|
|
#region 接口请求返回数据固化
|
|
if (data != null && data.Count > 0)
|
|
{
|
|
foreach (var _data in data)
|
|
{
|
|
if (!string.IsNullOrEmpty(_data["COMMODITY_CODE"].ToString()))
|
|
{
|
|
string insert_sql = string.Format(@"INSERT INTO COOP_MERCHANT.T_COMMODITYSALE (
|
|
STARTDATE,COMMODITY_CODE,TOTALCOUNT,SERVERPART_ID,PROVINCE_CODE,
|
|
TOTALSELLAMOUNT,TICKETCOUNT,TOTALOFFAMOUNT,COMMODITY_NAME,COMMODITY_BARCODE,SERVERPARTSHOP_ID)
|
|
VALUES (TO_DATE('{0}','YYYY-MM-DD hh24:mi:ss'),'{1}',{2},{3},{4},{5},{6},{7},'{8}','{9}',{10})",
|
|
_data["STARTDATE"], _data["COMMODITY_CODE"], _data["TOTALCOUNT"], item["SERVERPART_ID"].ToString(),
|
|
item["PROVINCE_CODE"].ToString(),
|
|
_data["TOTALSELLAMOUNT"], _data["TICKETCOUNT"], _data["TOTALOFFAMOUNT"], _data["COMMODITY_NAME"],
|
|
_data["COMMODITY_BARCODE"], item["SERVERPARTSHOP_ID"].ToString());
|
|
_slist.Add(insert_sql);
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
item["SERVERPART_NAME"].ToString() + "-" + item["SHOPNAME"].ToString() + "获取接口" + strTime + "号数据成功");
|
|
}));
|
|
|
|
|
|
}
|
|
if (_slist.Count > 0)
|
|
{
|
|
_OracleHelper_CM.ExecuteSqlTran(_slist.ToArray());
|
|
}
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品" + strTime + "号数据成功"); }));
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品手动生成结束"); }));
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品" + strTime + "号数据失败,原因:" + ex.ToString()); }));
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 方法 -> 根据服务区取数据URL
|
|
/// <summary>
|
|
/// 根据服务区取数据URL
|
|
/// </summary>
|
|
/// <param name="serverpartid"></param>
|
|
/// <returns></returns>
|
|
public static string GetUrl(string serverpartid)
|
|
{
|
|
try
|
|
{
|
|
string strUrl = string.Empty;
|
|
return System.Configuration.ConfigurationManager.AppSettings[serverpartid];
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return "";
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
private void button1_Click(object sender, EventArgs e)
|
|
{
|
|
try
|
|
{
|
|
ParameterizedThreadStart threadStart = new ParameterizedThreadStart(EndMPSEPARATE);
|
|
Thread threadSD = new Thread(threadStart);
|
|
threadSD.Start();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.WriteSendLog(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
#region 方法 -> 生成数据
|
|
private void EndMPSEPARATE(object obj)
|
|
{
|
|
string strTime = string.Empty;
|
|
DateTime sTime = Convert.ToDateTime(this.dtStarTime.Value.ToString("yyyy-MM-dd"));
|
|
DateTime eTime = Convert.ToDateTime(this.dtEndTime.Value.ToString("yyyy-MM-dd"));
|
|
try
|
|
{
|
|
TimeSpan sp = eTime.Subtract(sTime);
|
|
for (int i = 0; i <= sp.Days; i++)
|
|
{
|
|
strTime = sTime.AddDays(i).ToString("yyyy-MM-dd");
|
|
this.Invoke(new Action(() => { this.lbMessage.Text = ""; }));
|
|
if (lvMessage.Items.Count > 5000)
|
|
{
|
|
lvMessage.Items.Clear();
|
|
}
|
|
string sql = string.Format(@"SELECT
|
|
A.SERVERPART_ID,A.SERVERPARTSHOP_ID,A.PROVINCE_CODE,A.SERVERPART_NAME,A.SHOPNAME
|
|
FROM
|
|
MOBILESERVICE_PLATFORM.T_SERVERPARTSHOP A
|
|
WHERE
|
|
A.ISVALID = 1 AND A.BUSINESS_STATE = 1000 AND
|
|
','||A.STATISTICS_TYPE||',' like '%,3020,%' AND PROVINCE_CODE IN ({0})
|
|
GROUP BY
|
|
A.SERVERPART_ID,A.SERVERPARTSHOP_ID,A.PROVINCE_CODE,A.SERVERPART_NAME,A.SHOPNAME", strPROVINCE_CODES);
|
|
|
|
List<string> _slist = new List<string>();
|
|
foreach (DataRow item in _OracleHelper.ExcuteSqlGetDataSet(sql).Tables[0].Rows)
|
|
{
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"正在删除" + item["SERVERPART_NAME"].ToString() + "-" + item["SHOPNAME"].ToString() + "" + strTime + "号数据");
|
|
}));
|
|
_OracleHelper_CM.ExcuteSql(string.Format(@"DELETE FROM COOP_MERCHANT.T_COMMODITYSALE
|
|
WHERE STARTDATE >= TO_DATE('{0}','YYYY-MM-DD hh24:mi:ss') AND
|
|
STARTDATE < TO_DATE('{0}','YYYY-MM-DD hh24:mi:ss') + 1 AND
|
|
PROVINCE_CODE = {1} AND SERVERPARTSHOP_ID = {2} AND SERVERPART_ID = {3}", strTime,
|
|
item["PROVINCE_CODE"].ToString(), item["SERVERPART_ID"].ToString(), item["SERVERPARTSHOP_ID"].ToString()));
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add("删除" + item["SERVERPART_NAME"].ToString() +
|
|
"-" + item["SHOPNAME"].ToString() + strTime + "号数据成功");
|
|
}));
|
|
|
|
string strUrl = GetUrl(item["PROVINCE_CODE"].ToString());
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendFormat("?action_type={0}", "GetCommoditySaleData");
|
|
sb.AppendFormat("&serverPartId={0}", item["SERVERPART_ID"].ToString());
|
|
sb.AppendFormat("&serverPartshopId={0}", item["SERVERPARTSHOP_ID"].ToString());
|
|
sb.AppendFormat("&startDate={0}", strTime);
|
|
sb.AppendFormat("&endDate={0}", strTime);
|
|
string parameters = sb.ToString();
|
|
|
|
string strHtml = AppCode.Common.HttpUrlPost(parameters.Replace("?", ""), strUrl + parameters);
|
|
|
|
var data = QRWL.Common.JsonHelper.GetData2JArray(strHtml, "list");
|
|
DataTable _DataTable = new DataTable();
|
|
|
|
#region 接口请求返回数据固化
|
|
if (data != null && data.Count > 0)
|
|
{
|
|
foreach (var _data in data)
|
|
{
|
|
if (!string.IsNullOrEmpty(_data["COMMODITY_CODE"].ToString()))
|
|
{
|
|
string insert_sql = string.Format(@"INSERT INTO COOP_MERCHANT.T_COMMODITYSALE (
|
|
STARTDATE,COMMODITY_CODE,TOTALCOUNT,SERVERPART_ID,PROVINCE_CODE,
|
|
TOTALSELLAMOUNT,TICKETCOUNT,TOTALOFFAMOUNT,COMMODITY_NAME,COMMODITY_BARCODE,SERVERPARTSHOP_ID)
|
|
VALUES (TO_DATE('{0}','YYYY-MM-DD hh24:mi:ss'),'{1}',{2},{3},{4},{5},{6},{7},'{8}','{9}',{10})",
|
|
_data["STARTDATE"], _data["COMMODITY_CODE"], _data["TOTALCOUNT"], item["SERVERPART_ID"].ToString(),
|
|
item["PROVINCE_CODE"].ToString(),
|
|
_data["TOTALSELLAMOUNT"], _data["TICKETCOUNT"], _data["TOTALOFFAMOUNT"], _data["COMMODITY_NAME"],
|
|
_data["COMMODITY_BARCODE"], item["SERVERPARTSHOP_ID"].ToString());
|
|
_slist.Add(insert_sql);
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
this.Invoke(new Action(() =>
|
|
{
|
|
lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
item["SERVERPART_NAME"].ToString() + "-" + item["SHOPNAME"].ToString() + "获取接口" + strTime + "号数据成功");
|
|
}));
|
|
|
|
|
|
}
|
|
if (_slist.Count > 0)
|
|
{
|
|
_OracleHelper_CM.ExecuteSqlTran(_slist.ToArray());
|
|
}
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品" + strTime + "号数据成功"); }));
|
|
}
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品手动生成结束"); }));
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
this.Invoke(new Action(() => { lvMessage.Items.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
|
|
"插入仓储单品" + strTime + "号数据失败,原因:" + ex.ToString()); }));
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
}
|
|
}
|