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(); }