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