public void getrpt_forshop()
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "开始从服务器获取数据... \r\n");
richTextBox3.Focus();
}));
var suc = new SoapUnitTOM.SoapUnitClient();
// string connstr = getelement("connstr");
// richTextBox1.AppendText("oracle连接字符串：" + connstr + " ");
try
{
oraexec ora = new oraexec(connstr);
ora.ExecuteNonQuery("delete from JXC_FOR_SAP where CARDCODE='" + textBox1.Text + "'");
// DataTable dt = ora.GetDt("select count(1) from jxc_for_sap");
// richTextBox1.AppendText("select count(1) from jxc_for_sap的结果:" + dt.Rows.Count.ToString());
DataTable dtrpt = new DataTable();
// DataTable dt = suc.GetData(1, "DXKH", null);
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--开始查询报表... \r\n");
richTextBox3.Focus();
}));
// for (int i = 0; i < dt.Rows.Count; i++)
// for (int i = 0; i < 10; i++)
// {
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--" + "查询店铺(" + textBox1.Text + ")报表中...\r\n ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox3.Focus();
}));
string[] args = { Std.Value.ToString("yyyy-MM-dd"), Etd.Value.ToString("yyyy-MM-dd"), textBox1.Text, Std.Value.ToString("yyyy-MM") };
dtrpt = suc.GetData(1, "Single_Shop", args);
// DataTable dtrptcopy = dtrpt.Copy();
Task.Run(() => BulkToDB(dtrpt, "JXC_FOR_SAP"));
// Task.Run(() => readexcel(dtrpt));
// }
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--查寻结束 \r\n");
}));
}
catch (Exception er)
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(er.ToString() + " ");
}));
}
}
private void button4_Click_1(object sender, EventArgs e)
{
try
{
oraexec ora = new oraexec(connstr);
int i = ora.ExecuteNonQuery("truncate table jxc_for_sap");
richTextBox3.AppendText(DateTime.Now.ToString() + "--清除数据结果:" + i.ToString() + " ");
DataTable dt = new DataTable();
dt = ora.GetDt("select * from t_mom_outinwmsresultorder where state=2 ");
if (dt.Rows.Count > 0)
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--查询到" + dt.Rows.Count.ToString() + "条记录 ");
richTextBox3.Focus();
}
}
catch (Exception er)
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(er.ToString() + " ");
}));
}
}
private void getsaledetail()
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "开始从服务器获取数据... ");
richTextBox3.Focus();
}));
var suc = new SoapUnitTOM.SoapUnitClient();
// string connstr = getelement("connstr");
// richTextBox1.AppendText("oracle连接字符串：" + connstr + " ");
try
{
oraexec ora = new oraexec(connstr);
int k = ora.ExecuteNonQuery("truncate table JXC_FOR_SALEDETAIL");
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("清除已有记录结果:" + k.ToString() + " ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox4.Focus();
}));
// DataTable dt = ora.GetDt("select count(1) from jxc_for_sap");
// richTextBox1.AppendText("select count(1) from jxc_for_sap的结果:" + dt.Rows.Count.ToString());
DataTable dtrpt = new DataTable();
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--" + "查询店铺销售上报报表中... ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox3.Focus();
}));
string[] args = { Std.Value.ToString("yyyy-MM") };
dtrpt = suc.GetData(1, "Get_saleDetail", args);
// for (int i=0;i<dtrpt.Rows.Count;i++)
//for (int i = 0; i < 2; i++)
//{
// string sql = "insert into JXC_FOR_SALEDETAIL values ('" + dtrpt.Rows[i][0].ToString() + "','" + dtrpt.Rows[i][1].ToString() + "','" + dtrpt.Rows[i][2].ToString() + "','" + dtrpt.Rows[i][3].ToString() + "','" + dtrpt.Rows[i][4].ToString() + "'," + double.Parse(dtrpt.Rows[i][5].ToString()) + ");";
// //this.Invoke(new Action(() =>
// //{
// // // richTextBox4.AppendText(sql+" ");
// // // richTextBox4.AppendText(sql + " ");
// // // richTextBox4.Focus();
// //}));
// int j = ora.ExecuteNonQuery(sql);
// if (j != -1)
// {
// this.Invoke(new Action(() =>
// {
// richTextBox4.AppendText("插入第" + (i + 1).ToString() + "记录,执行结果:" + j.ToString() + " ");
// richTextBox4.AppendText(sql+" ");
// richTextBox4.Focus();
// }));
// }
//}
DataTable dtrptcopy = dtrpt.Clone();
DataRow[] dr = dtrpt.Select();
for (int i = 0; i < 100; i++)
{
dtrptcopy.ImportRow((DataRow)dr[i]);
}
Task.Run(() => BulkToDB(dtrptcopy, "JXC_FOR_SALEDETAIL"));
// Task.Run(() => readexcel(dtrpt));
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("插入记录数:" + dtrpt.Rows.Count.ToString() + " ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox3.Focus();
}));
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--运行结束 ");
}));
}
catch (Exception er)
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(er.ToString() + " ");
}));
}
finally
{
}
}
public void getrptone()
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "开始从服务器获取数据... \r\n");
richTextBox3.Focus();
this.button2.Enabled = false;
this.button5.Enabled = false;
this.button6.Enabled = false;
this.button7.Enabled = false;
this.button8.Enabled = false;
}));
try
{
oraexec ora = new oraexec(connstr);
ora.ExecuteNonQuery("truncate table test");
DataTable dtrpt = new DataTable();
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(DateTime.Now.ToString() + "--提取数据中...\r\n ");
richTextBox3.Focus();
}));
Sqlexec sqlexc = new Sqlexec(StrConn);
string text = @"DECLARE @S_Date DATETIME
DECLARE @E_Date DATETIME
DECLARE @DOCMONTH VARCHAR(10)
SET @S_Date = CAST('{0}' AS DATETIME)
SET @E_Date = CAST('{1}' AS DATETIME)
SET @DOCMONTH='{2}'
SELECT
T0.ItemCode
-- , T1.ItemName
, T3.BinCode
, T4.CardCode
, T4.CardName
-- , T1.U_CodeBard
,SUM(T0.InvPLInQty)-SUM(T0.InvPLOutQty) QC_QTY
into #TEMP_00
FROM [dbo].[MY_B1_OinmWithBinTransfer] T0
INNER JOIN [dbo].[VWE_OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
LEFT OUTER JOIN [dbo].[OBTL] T2 ON T0.[InvPLMessageID] = T2.[MessageID]
INNER JOIN OBIN T3 ON T2.BinAbs = T3.AbsEntry
LEFT JOIN VWE_OCRD T4 ON T4.U_BinCode = T3.BinCode
LEFT JOIN (select U_Code,U_Name from U_CPJC) t5 on t1.u_xmbrand=t5.u_code
LEFT JOIN (SELECT CARDCODE,U_SALEAREACODE,U_SALETEAMCODE FROM VWE_CRD1 GROUP BY CARDCODE,U_SALEAREACODE,U_SALETEAMCODE) TG ON T4.CARDCODE=TG.CARDCODE
WHERE
T0.Warehouse = 'zdmd'
AND ( T4.U_CONSIGN = 'Y'
OR T4.U_CONSIGN IS NULL
)
AND CONVERT(CHAR(10), T0.DocDate, 111) < CONVERT(CHAR(10), @S_Date, 111)
group by
T0.ItemCode
, T1.ItemName
, T3.BinCode
, T4.CardCode
, t4.CardName
, T1.U_CodeBard
SELECT
T0.ItemCode
, T3.BinCode
, T4.CardCode
, T4.CardName
,SUM(T0.InvPLInQty) InvPLInQty
,SUM(T0.InvPLOutQty) InvPLOutQty
INTO #TEMP_01
FROM [dbo].[MY_B1_OinmWithBinTransfer] T0
INNER JOIN [dbo].[VWE_OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
LEFT OUTER JOIN [dbo].[OBTL] T2 ON T0.[InvPLMessageID] = T2.[MessageID]
INNER JOIN OBIN T3 ON T2.BinAbs = T3.AbsEntry
LEFT JOIN VWE_OCRD T4 ON T4.U_BinCode = T3.BinCode
LEFT JOIN (select U_Code,U_Name from U_CPJC) t5 on t1.u_xmbrand=t5.u_code
LEFT JOIN (SELECT CARDCODE,U_SALEAREACODE,U_SALETEAMCODE FROM VWE_CRD1 GROUP BY CARDCODE,U_SALEAREACODE,U_SALETEAMCODE) TG ON T4.CARDCODE=TG.CARDCODE
WHERE
T0.Warehouse = 'zdmd'
AND ( T4.U_CONSIGN = 'Y'
OR T4.U_CONSIGN IS NULL
)
AND CONVERT(CHAR(10), T0.DocDate, 111) >= CONVERT(CHAR(10), @S_Date, 111)
AND CONVERT(CHAR(10), T0.DocDate, 111) <= CONVERT(CHAR(10), @E_Date, 111)
group by
T0.ItemCode
, T1.ItemName
, T3.BinCode
, T4.CardCode
, t4.CardName
, T1.U_CodeBard
SELECT
T0.ItemCode
, T3.BinCode
, T4.CardCode
, T4.CardName
,AVG(t0.price) AS AVGPRICE
, SUM(InvPLInQty - InvPLOutQty) AS QM_QTY
, SUM(TRANSVALUE) AS STOCK
INTO #TEMP_011
FROM [dbo].[MY_B1_OinmWithBinTransfer] T0
INNER JOIN [dbo].[VWE_OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
LEFT OUTER JOIN [dbo].[OBTL] T2 ON T0.[InvPLMessageID] = T2.[MessageID]
INNER JOIN OBIN T3 ON T2.BinAbs = T3.AbsEntry
LEFT JOIN VWE_OCRD T4 ON T4.U_BinCode = T3.BinCode
LEFT JOIN (select U_Code,U_Name from U_CPJC) t5 on t1.u_xmbrand=t5.u_code
LEFT JOIN (SELECT CARDCODE,U_SALEAREACODE,U_SALETEAMCODE FROM VWE_CRD1 GROUP BY CARDCODE,U_SALEAREACODE,U_SALETEAMCODE) TG ON T4.CARDCODE=TG.CARDCODE
WHERE
T0.Warehouse = 'zdmd'
AND ( T4.U_CONSIGN = 'Y'
OR T4.U_CONSIGN IS NULL
)
AND CONVERT(CHAR(10), T0.DocDate, 111) <= CONVERT(CHAR(10), @E_Date, 111)
group by
T0.ItemCode
, T1.ItemName
, T3.BinCode
, T4.CardCode
, t4.CardName
, T1.U_CodeBard
select @DOCMONTH DOCMONTH ,a.BinCode,
a.CardCode as CardCode,
a.CardName as CardName,
a.ItemCode as ItemCode,
b.QC_QTY QC_Qty,c.InvPLInQty AS In_QTY,c.InvPLOutQty as Out_Qty ,
a.QM_QTY ,a.STOCK,a.AVGPRICE
INTO #TEMP_02
from #TEMP_011 a left outer join #TEMP_00 b on a.BinCode=b.BinCode and a.CardCode=b.CardCode and a.ItemCode=b.ItemCode
left outer join #TEMP_01 c on a.BinCode=c.BinCode and a.CardCode=c.CardCode and a.ItemCode=c.ItemCode
SELECT
@DOCMONTH DOCMONTH
, T0.CardCode
,T0.CARDNAME
,T1.ItemCode
,T1.STOCKPRICE
, T1.Quantity as saleqty
, T1.GTotal as saletotal
into #temp_03
FROM OINV T0
LEFT JOIN vwe_INV1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN vwe_ocrd t2 ON t0.cardcode = t2.cardcode
LEFT JOIN VWE_OITM T3 ON T1.ItemCode=T3.ItemCode
LEFT JOIN (SELECT U_CODE,U_NAME FROM U_CPJC) T4 ON T3.U_XMBrand=T4.U_CODE
WHERE
CONVERT(CHAR(10), T0.DocDate, 111) >= CONVERT(CHAR(10), @S_Date, 111)
AND CONVERT(CHAR(10), T0.DocDate, 111) <= CONVERT(CHAR(10), @E_Date, 111)
AND T0.DOCTYPE <> 'S'
AND t2.u_consign = 'Y'
union all
SELECT @DOCMONTH DOCMONTH,
T0.CardCode
,T0.CARDNAME
,0 as STOCKPRICE
,T1.ItemCode ,
-1*T1.Quantity as saleqty,
-1*t1.quantity * priceafvat AS saletotal
FROM ORIN T0
LEFT JOIN vwe_rin1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN vwe_ocrd t2 ON t0.cardcode = t2.cardcode
WHERE
CONVERT(CHAR(10), T0.DocDate, 111) >= CONVERT(CHAR(10), @S_Date, 111)
AND CONVERT(CHAR(10), T0.DocDate, 111) <= CONVERT(CHAR(10), @E_Date, 111)
AND T0.DOCTYPE <> 'S'
AND t2.u_consign = 'Y'
select DOCMONTH,CardCode,CardName,ItemCode,SUM(saleqty) SALEQTY,sum(saletotal) saletotal into #temp_05 from #temp_03 group by DOCMONTH,CardCode,CardName,ItemCode
select isnull(a.DOCMONTH ,b.DOCMONTH) DOCMONTH,isnull(a.BinCode,'') bincode,isnull(a.CardCode,b.CardCode) CardCode,isnull(a.CardName,b.CardName) cardename,
isnull(a.ItemCode,b.ItemCode ) itemcode,isnull(a.QC_QTY,0) QC_QTY,isnull(a.In_Qty,0) IN_QTY,isnull(a.Out_Qty,0) OUT_QTY,isnull(a.QM_Qty,0) QM_QTY ,
ISNULL(b.saleqty,0) SALEQTY,ISNULL(b.saletotal,0) SALETOTAL ,a.STOCK,a.AVGPRICE
into #temp_04
from #temp_02 a
full join #temp_05 b on a.CardCode=b.CardCode and a.ItemCode=b.ItemCode and a.DOCMONTH=b.DOCMONTH
select
T4.DQJL 大区经理
,T4.YWZG 业务主管
, T0.CardCode 店铺代码
,t0.cardename 店铺名称
,t0.QC_QTY 期初数量
,t0.IN_QTY 入库数量
,t0.OUT_QTY 出库数量
,t0.QM_QTY 期末数量
,t0.QM_QTY*T1.U_retailprice 期末库存金额
,t0.STOCK 库存成本金额
,t0.SALEQTY 销售上报数量
,t0.SALETOTAL 销售上报金额
,t0.itemcode 货品编号
,t1.ItemName 货品名称
,T5.u_name 品牌
,T1.U_CodeBard 条码
,T1.U_retailprice 零售价格
,T0.AvgPrice 库存成本
,T1.U_ModleCode
-- , TG.U_SALEAREACODE
-- , TG.U_SALETEAMCODE
FROM #temp_04 T0
LEFT OUTER JOIN [dbo].[VWE_OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
-- LEFT OUTER JOIN (select ItemCode, AvgPrice from [dbo].[VWE_OITW] where whscode='zdmd') T2 ON T0.[ItemCode] = T2.[ItemCode]
LEFT OUTER JOIN U_MDRY1 T4 ON T4.CardCode = T0.CardCode
LEFT OUTER JOIN (select U_Code,U_Name from U_CPJC) T5 on t1.u_xmbrand=t5.u_code
LEFT OUTER JOIN (SELECT CARDCODE,U_SALEAREACODE,U_SALETEAMCODE FROM VWE_CRD1 GROUP BY CARDCODE,U_SALEAREACODE,U_SALETEAMCODE) TG ON T4.CARDCODE=TG.CARDCODE
-- LEFT OUTER JOIN
--(
-- select U_code ,b.UserName from U_QPFB a
-- left join T_cuse b
-- on a.U_ManagerID = b.UserID
--) T6 ON T6.U_CODE=TG.U_SALEAREACODE
--LEFT OUTER JOIN
--(
-- select U_code ,b.UserName from U_QPFB a
-- left join T_cuse b
-- on a.U_ManagerID = b.UserID
--) T7 ON T7.U_CODE=TG.U_SALETEAMCODE
ORDER BY 店铺代码
-- left outer join
-- ) c group by DOCMONTH,cardcode,cardename order by cardcode
drop table #TEMP_00
drop table #TEMP_01
drop table #TEMP_02
drop table #TEMP_03
drop table #TEMP_04
drop table #TEMP_05
drop table #TEMP_011
";
string newsql = text.Replace("{0}", Std.Value.ToString("yyyy/MM/dd")).Replace("{1}", Etd.Value.ToString("yyyy/MM/dd")).Replace("{2}", Std.Value.ToString("yyyy-MM"));
dtrpt = sqlexc.SqlGetDataSet(newsql);
Task.Run(() => BulkToDB(dtrpt, "test"));
}
catch (Exception er)
{
this.Invoke(new Action(() =>
{
richTextBox3.AppendText(er.ToString() + " ");
this.button2.Enabled = true;
this.button5.Enabled = true;
this.button6.Enabled = true;
this.button7.Enabled = true;
this.button8.Enabled = true;
}));
}
}
//test
private void button2_Click(object sender, EventArgs e)
{
string remark = dateTimePicker1.Value.ToString("yyyy/MM/dd") + " " + comboBox1.Text + "盘点";
string PDDH = "PD" + comboBox1.SelectedValue.ToString() + "-" + dateTimePicker1.Value.Year.ToString() + (dateTimePicker1.Value.Month < 10 ? "0" + dateTimePicker1.Value.Month.ToString() : dateTimePicker1.Value.Month.ToString()) + (dateTimePicker1.Value.Day < 10 ? "0" + dateTimePicker1.Value.Day.ToString() : dateTimePicker1.Value.Day.ToString()) + "0001";
string insql = @"insert into t_mat_checkouttable(orderid,storageid,comparedate,checktime,remark,makedatetime,productor,
checkstate,updatetime,dtype,exceptionorder,checkname,checktype,docentry,systype) values(";
insql += $"'{PDDH}','{comboBox1.SelectedValue.ToString()}',to_date('{dateTimePicker1.Value.ToString("yyyy-MM-dd")}','yyyy-mm-dd'),to_date('{dateTimePicker1.Value.ToString("yyyy-MM-dd")}','yyyy-mm-dd'),'{remark}',to_date('{dateTimePicker1.Value.ToString("yyyy-MM-dd")}','yyyy-mm-dd'),'Admin',";
insql += $"1,to_date('{dateTimePicker1.Value.ToString("yyyy-MM-dd")}','yyyy-mm-dd'),'1','{remark}','{remark}',3,'1','K')";
// richTextBox1.Text = insql;
ora.ExecuteNonQuery(insql);
ShowInvInfo();
}