private void button1_Click(object sender, EventArgs e) { int col = 0; Sqlexec sqlex = new Sqlexec(getelement("sqlconnstr")); for (int i = 0; i < dataGridView6.Rows.Count; i++) { if (dataGridView6.Rows[i].Cells[0].Value.ToString() != "") { if (!Isbind(dataGridView6.Rows[i].Cells[2].Value.ToString(), dataGridView6.Rows[i].Cells[0].Value.ToString())) { sqlex.SqlExecuteSQL("insert into U_MMS_TB_USER_CLIENT values('" + dataGridView6.Rows[i].Cells[2].Value.ToString() + "','" + dataGridView6.Rows[i].Cells[0].Value.ToString() + "')"); this.Invoke(new Action(() => { dataGridView6.Rows[i].Cells[0].Style.BackColor = Color.Green; richTextBox2.AppendText(dataGridView6.Rows[i].Cells[1].Value.ToString() + "--" + dataGridView6.Rows[i].Cells[0].Value.ToString() + "绑定完成" + "\r\n"); })); } else { this.Invoke(new Action(() => { dataGridView6.Rows[i].Cells[0].Style.BackColor = Color.Red; richTextBox2.AppendText(dataGridView6.Rows[i].Cells[1].Value.ToString() + "--" + dataGridView6.Rows[i].Cells[0].Value.ToString() + "已存在绑定记录,无须再绑定" + "\r\n"); })); } } else { richTextBox2.AppendText(dataGridView6.Rows[i].Cells[1].Value.ToString() + "店铺代码为空,不能绑定" + "\r\n"); } } }
private void button1_Click(object sender, EventArgs e) { Sqlexec sqlex = new Sqlexec(getelement("sqlconnstr")); DataTable dt = sqlex.SqlGetDataSet("SELECT T1.WddCode, T1.DocEntry, T2.OrderType,T2.OwnerID ,T2.Data FROM T_CWDD T1 LEFT JOIN T_CDRF T2 ON T1.DocEntry = T2.Code WHERE T1.Status = N'Y' and T2.OrderType = '1146' AND T1.WddCode = '22979' "); DataSet ds = new DataSet(); string Data = dt.Rows[0]["Data"].ToString(); string docentry = GetDocEntry("U_CXCL"); using (MemoryStream ms = new MemoryStream(new UTF8Encoding().GetBytes(Data))) { ds.ReadXml(ms); if (!ds.Tables[0].Columns.Contains("Comment")) { ds.Tables[0].Columns.Add(new DataColumn("Comment")); } if (!ds.Tables[0].Columns.Contains("DocStatus")) { ds.Tables[0].Columns.Add(new DataColumn("DocStatus")); } dataGridView1.DataSource = ds.Tables[0]; foreach (DataRow dr in ds.Tables[0].Rows) { dr["DocEntry"] = docentry; } foreach (DataRow dr in ds.Tables[1].Rows) { dr["DocEntry"] = docentry; } foreach (DataRow dr in ds.Tables[2].Rows) { dr["DocEntry"] = docentry; } if (!SqlBulkCopyInsertMain(getelement("sqlconnstr"), ds.Tables[0].TableName, ds.Tables[0])) { MessageBox.Show("主表插入失败"); } dataGridView2.DataSource = ds.Tables[1]; if (!SqlBulkCopyInsert(getelement("sqlconnstr"), ds.Tables[1].TableName, ds.Tables[1])) { MessageBox.Show("明细表插入失败"); } dataGridView3.DataSource = ds.Tables[2]; if (!SqlBulkCopyInsert(getelement("sqlconnstr"), ds.Tables[2].TableName, ds.Tables[2])) { MessageBox.Show("明细表1插入失败"); } } }
public string getClientid(string U_clientName) { Sqlexec sqlex = new Sqlexec(getelement("sqlconnstr")); string sql = " select U_AddressCode from VWE_CRD1 where [Address]='" + U_clientName + "'"; DataTable dt = sqlex.SqlGetDataSet(sql); if (dt.Rows.Count > 0) { return(dt.Rows[0][0].ToString()); } else { return(""); } }
public bool Isbind(string userid, string U_Clientid) { Sqlexec sqlex = new Sqlexec(getelement("sqlconnstr")); string sql = " select 1 from U_MMS_TB_USER_CLIENT where U_USERID='" + userid + "' and U_CLIENTID='" + U_Clientid + "'"; DataTable dt = sqlex.SqlGetDataSet(sql); if (dt.Rows.Count > 0) { return(true); } else { return(false); } }
public string getUserName(string U_userid) { Sqlexec sqlex = new Sqlexec(getelement("sqlconnstr")); string sql = " select U_USENAME from U_MMS_TB_USER where U_USERID = '" + U_userid + "'"; DataTable dt = sqlex.SqlGetDataSet(sql); if (dt.Rows.Count > 0) { return(dt.Rows[0][0].ToString()); } else { return(""); } }
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; })); } }
public void readexcel() { if (openFileDialog1.FileName != "") { this.Invoke(new Action(() => { this.lblprocessmsg.Text = "开始导入数据..."; this.button3.Enabled = false; })); Sqlexec sqlex = new Sqlexec(getelement("connstr")); object missing = Type.Missing; Microsoft.Office.Interop.Excel.Application ExcelRS; Microsoft.Office.Interop.Excel.Workbook RSbook; Microsoft.Office.Interop.Excel.Worksheet RSsheet; // ExcelRS = null; //实例化ExcelRS对象 ExcelRS = new Microsoft.Office.Interop.Excel.Application(); //打开目标文件filePath RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //设置第一个工作溥 RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1); //激活当前工作溥 RSsheet.Activate(); StringBuilder errmsg = new StringBuilder(); // object missing = Type.Missing; errmsg.Append("错误信息:"); try { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("U_clientid", typeof(System.String))); dt.Columns.Add(new DataColumn("U_clientname", typeof(System.String))); dt.Columns.Add(new DataColumn("U_userid", typeof(System.String))); dt.Columns.Add(new DataColumn("U_name", typeof(System.String))); int col = 0; string U_clientid, U_clientName, U_userid; #region 读取工作薄内容 for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++) { this.Invoke(new Action(() => { this.lblprocessmsg.Text = "读取第" + row.ToString() + "行记录中..."; this.button3.Enabled = false; })); U_clientName = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Text; U_userid = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox4.Text)]).Text; DataRow dr = dt.NewRow(); dr[0] = getClientid(U_clientName); dr[1] = U_clientName; dr[2] = U_userid; dr[3] = getUserName(U_userid); dt.Rows.Add(dr); this.Invoke(new Action(() => { this.lblprocessmsg.Text = "读取" + U_clientName + "记录成功"; this.button3.Enabled = false; })); col++; // ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Font.Color = ColorTranslator.ToOle(Color.Red); } // DataTable dt= oleGetDataSet("select BARCODE,SPDM,GG1DM,GG2DM,N_SL,SL,MARK FROM WPHYCD"); ExcelRS.Visible = false; ExcelRS.DisplayAlerts = false; RSbook.Save(); this.Invoke(new Action(() => { dataGridView6.DataSource = dt; })); MessageBox.Show("成功导入记录:" + col.ToString() + "条!"); } catch (Exception er) { MessageBox.Show(er.ToString()); } finally { ExcelRS.Quit(); IntPtr t = new IntPtr(ExcelRS.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); this.Invoke(new Action(() => { this.lblprocessmsg.Text = "导入完成"; this.button3.Enabled = true; })); } } }