private void btnSelect_Click(object sender, EventArgs e)
{
string sqlstr = " SELECT a.Fnumber as 产品代码,t.fname AS 产品名称,a.Fprice AS 单价 FROM dbo.R_S_price a " +
" INNER join AIS_YXSP2.dbo.t_icitem t ON a.fnumber=t.fnumber where 1=1 ";
if (txtFnumber.Text.Trim() != "")
{
sqlstr = sqlstr + " AND t.fnumber LIKE '%" + txtFnumber.Text.Trim() + "%' ";
}
if (txtFname.Text.Trim() != "")
{
sqlstr = sqlstr + " AND t.fname LIKE '%" + txtFname.Text.Trim() + "%' ";
}
DataBase db = new DataBase();
try
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("正在查询......");
dgvPrice.DataSource = db.GetDataTable(sqlstr, "Price");
}
catch (Exception err)
{
MessageBox.Show("查询失败！" + err.ToString());
return;
}
WaitFormService.CloseWaitForm();
}
private void btnJS_Click(object sender, EventArgs e)
{
crystalReportViewer1.ReportSource = null;
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在计算，请稍候......");
string datestr = "";
datestr = this.dateTimePicker1.Value.ToShortDateString();
SqlParameter param2 = new SqlParameter("@date", SqlDbType.VarChar);
param2.Value = datestr;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
db.GetProcRow("spk3_2_BTSKBM_czq", inputParameters2); //spk3_2_BTSKBM_czq
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("计算失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("计算完成!", "软件提示");
}
private void btnBak_Click(object sender, EventArgs e)
{
DataBase db2 = new DataBase(k3constr);
string sqlstr1;
string sqlstr2;
sqlstr1 = " INSERT INTO YXERP.dbo.k3_Log SELECT " + ztID + ", t1.FLogID,t1.FDate,u.FName username,t2.FFunctionName ,t1.FStatement," +
" t1.FDescription ,FMachineName ,FIPAddress FROM t_Log T1 INNER JOIN t_LogFunction t2 ON t1.FFunctionID = t2.FFunctionID " +
" LEFT JOIN t_User u ON t1.FUserID = U.FUserID ";
sqlstr2 = " DELETE t_Log ";
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在备份，请稍候......");
try
{
db2.ExecDataBySql(sqlstr1);
db2.ExecDataBySql(sqlstr2);
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("备份失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("备份成功!", "软件提示");
}
private void button1_Click(object sender, EventArgs e) //查询
{
int FType = 0;
if (rbByHour.Checked) //小时
{
FType = 1;
}
else if (rbByDay.Checked) //每天
{
FType = 2;
}
else if (rbByMonth.Checked) //每月
{
FType = 3;
}
else if (rbByYear.Checked) //每年
{
FType = 4;
}
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@FType", SqlDbType.Int);
param1.Value = FType;
SqlParameter param2 = new SqlParameter("@FDate1", SqlDbType.VarChar);
param2.Value = txtFDate1.Text;
SqlParameter param3 = new SqlParameter("@FDate2", SqlDbType.VarChar);
param3.Value = txtFDate2.Text;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param1);
parameters2.Add(param2);
parameters2.Add(param3);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
bdsFind.DataSource = db.GetDataTable("sp_YZ_ImportData_Select", inputParameters2);
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void btnInput_Click(object sender, EventArgs e)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
try
{
if (!validationData())
{
return;
}
// DataBase db2 = new DataBase(ZtRyconstring);
db.ExecDataBySql("sp_K3_insertreceipt");
//SetFormCaption;
WaitFormService.SetWaitFormCaption("正在导入数据！");
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = " 表格数据导入失败!";
MessageBox.Show("导入K3失败!" + ex.ToString(), "软件提示");
return;
}
finally
{
WaitFormService.CloseWaitForm();
}
MessageBox.Show("导入成功");
}
private void btnBselect_Click(object sender, EventArgs e)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在计算，请稍候......");
try
{
string strfilter = " SELECT * FROM v_rymdbyj WHERE 时间 BETWEEN '" + this.dateTimePicker1.Value.ToShortDateString() +
" 00:00:00.000' AND '" + this.dateTimePicker2.Value.ToShortDateString() + " 23:59:59.000' ";
if (this.textBox1.Text != "")
{
strfilter = strfilter + " AND 店名 LIKE '%" + this.textBox1.Text + "%' ";
}
if (this.textBox2.Text != "")
{
strfilter = strfilter + " AND 机器号=" + this.textBox2.Text;
}
ds = db.GetDataSet(strfilter, "tab");
this.dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在更新......");
DataBase db = new DataBase();
SqlParameter param1 = new SqlParameter("@begdate", SqlDbType.DateTime);
param1.Value = DateTime.Parse(this.dateTimePicker1.Value.ToShortDateString());
SqlParameter param2 = new SqlParameter("@enddate", SqlDbType.DateTime);
param2.Value = DateTime.Parse(this.dateTimePicker2.Value.ToShortDateString());
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
db.GetProcRow("sp_update_price_spwuru_ryxsck", inputParameters);
WaitFormService.CloseWaitForm();
MessageBox.Show("更新单价成功！");
}
catch
{
WaitFormService.CloseWaitForm();
MessageBox.Show("更新单价失败！");
return;
}
}
//加载列表数据
private void getDate(int FYear)
{
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@FYear", SqlDbType.Int);
param1.Value = FYear;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param1);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
bdsMaster.DataSource = db.GetDataTable("sp_YZPerMonthProject_Select", inputParameters2);
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void button1_Click(object sender, EventArgs e)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在计算，请稍候......");
//string datestr = "";
//datestr = this.dateTimePicker1.Value.ToShortDateString();
//SqlParameter param1 = new SqlParameter("@BegDate", SqlDbType.VarChar);
//param1.Value = this.dateTimePicker1.Value.ToShortDateString();
//SqlParameter param2 = new SqlParameter("@EndDate", SqlDbType.VarChar);
//param2.Value = this.dateTimePicker2.Value.ToShortDateString();
////创建泛型
//List<SqlParameter> parameters2 = new List<SqlParameter>();
//parameters2.Add(param1);
//parameters2.Add(param2);
////把泛型中的元素复制到数组中
//SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
// this.dataGridView1.DataSource=db.GetDataTable("cdproc", inputParameters2);
// ds = db.GetDataSet("SELECT Fdbstr FROM dbo.YXZTLIST WHERE ID=1", "con");
// string MyConn = ds.Tables[0].Rows[0]["Fdbstr"].ToString();
string MyConn = "Data Source=188.188.1.4;Initial Catalog=AIS_YXSP2;User ID=sa;Password=Asd123;Connect Timeout=0";
//MyConn = ZtRyconstring;
SqlDataAdapter SelectAdapter = new SqlDataAdapter();
SqlConnection MyConnection = new SqlConnection(MyConn);
SqlCommand MyCommand = new SqlCommand("cdproc", MyConnection);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add("@BegDate", SqlDbType.VarChar);
MyCommand.Parameters.Add("@EndDate", SqlDbType.VarChar);
MyCommand.Parameters["@BegDate"].Value = this.dateTimePicker1.Value.ToShortDateString();
MyCommand.Parameters["@EndDate"].Value = this.dateTimePicker2.Value.ToShortDateString();
MyConnection.Open();
SelectAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
SelectAdapter.SelectCommand = MyCommand;
SelectAdapter.SelectCommand.CommandTimeout = 500;
//SelectAdapter.SelectCommand.ExecuteNonQuery();
MyConnection.Close();
SelectAdapter.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
//MessageBox.Show("计算完成!", "软件提示");
}
private void btnInput_Click(object sender, EventArgs e)
{
string excelFileName = "";
openFileDialog1.FileName = "";
//openFileDialog1.Filter = "EXCEL文件(*.xls,*.xlsx)|*.xls,*.xlsx";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
excelFileName = openFileDialog1.FileName;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection Excel_conn = new OleDbConnection(strConn);
string SheetName = "";
SheetName = GetFirstSheetNameFromExcelFileName(excelFileName, 1);
string strExcel = string.Format("select 产品代码,单价 from [{0}" + "$] ", SheetName);
OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();//excel
da.Fill(ds);
//读取单价到表R_S_price_d
List <string> strSqls = new List <string>();
strSqls.Add(" TRUNCATE TABLE R_S_price_d ");
DataRow dr = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
dr = ds.Tables[0].Rows[i];
strSqls.Add(" INSERT INTO R_S_price_d ('" + dr["产品代码"].ToString() + "'," + dr["单价"].ToString() + ") ");
}
//更新单价
//
strSqls.Add(" TRUNCATE TABLE R_S_price ");
// strSqls.Add(" UPDATE R_S_price SET Fprice=d.Fprice FROM dbo.R_S_price a INNER JOIN dbo.R_S_price_d d ON a.Fnumber=d.Fnumber ");
// strSqls.Add(" DELETE R_S_price_d FROM dbo.R_S_price a INNER JOIN dbo.R_S_price_d d ON a.Fnumber=d.Fnumber ");
strSqls.Add(" INSERT INTO R_S_price(Fnumber,Fprice) SELECT Fnumber,Fprice FROM R_S_price_d ");
DataBase db = new DataBase();
if (!db.ExecDataBySqls(strSqls))
{
WaitFormService.CloseWaitForm();
MessageBox.Show("更新单价失败！", "软件提示");
return;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("更新单价成功！", "软件提示");
}
}
private void button4_Click(object sender, EventArgs e)
{
if (this.textBox4.Text.Trim() == "" || this.textBox3.Text.Trim() == "")
{
MessageBox.Show("请输入年月！");
return;
}
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
// this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
SqlParameter param1 = new SqlParameter("@fyear", SqlDbType.VarChar);
param1.Value = this.textBox4.Text.Trim();
SqlParameter param2 = new SqlParameter("@fmonth", SqlDbType.VarChar);
param2.Value = this.textBox3.Text.Trim();
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
ds2 = db.GetProcDataSet("sp_sel_spmdsyb_new", inputParameters);
this.dataGridView2.DataSource = ds2.Tables[0];
//this.toolStripStatusLabel1.Text = " 读取数据完成.";
WaitFormService.CloseWaitForm();
}
catch (Exception err)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("操作失败！" + err.ToString());
// this.toolStripStatusLabel1.Text = " 读取数据失败.";
}
}
private void button1_Click(object sender, EventArgs e) //成本查询
{
splitContainer5Data.Panel1Collapsed = true;
if (splitContainer5Data.Panel2Collapsed == true)
{
splitContainer5Data.Panel2Collapsed = false;
}
Ftype = 1;
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
//this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
SqlParameter param1 = new SqlParameter("@BegDate", SqlDbType.DateTime);
param1.Value = this.dateTimePicker1.Value;
SqlParameter param2 = new SqlParameter("@EndDate", SqlDbType.DateTime);
param1.Value = this.dateTimePicker1.Value;
param2.Value = this.dateTimePicker2.Value;
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
ds = db.GetProcDataSet("sp_sel_yx_rs_ysprice", inputParameters);
this.dataGridView2Price.DataSource = ds.Tables[0];
//this.toolStripStatusLabel1.Text = " 读取成本数据完成.";
WaitFormService.CloseWaitForm();
}
catch (Exception err)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("操作失败！" + err.ToString());
//this.toolStripStatusLabel1.Text = " 读取成本数据失败.";
}
}
private void btnImport_Click(object sender, EventArgs e) //导入数据
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
List <string> strSqls = new List <string>();
if (this.dataGridView1.RowCount == 0)
{
return;
}
strSqls.Add(" DELETE t_YZ_ImportData WHERE FDate='" + dttTemp.Rows[0]["FDate"] + "'"); //删除要导入的日期的时间
string str;
str = "";
DataRow dr = null;
int k = 0;
for (int i = 0; i < dttTemp.Rows.Count; i++)
{
dr = dttTemp.Rows[i];
strSqls.Add(" INSERT INTO [YXERP].[dbo].[t_YZ_ImportData] ([FName],[FDateTime],[AvgSO2] ,[DischargeSO2] ,[AvgSO2ZSND] ,[DischargeSO2ZSND] ,[AvgNOX] ,[DischargeNOX],[AvgNOXZSND],[DischargeNOXZSND],[AvgSMOKE],[DischargeSMOKE],[AvgSMOKEZSND],[DischargeSMOKEZSND],[AvgO2],[AvgFlow],[SumFlow],[FYear],[FMonth],[FDate]) VALUES ( '" + dr["FName"] + "','" + dr["FDateTime"]
+ "','" + dr["AvgSO2"] + "'," + dr["DischargeSO2"] + ",'" + dr["AvgSO2ZSND"] + "'," + dr["DischargeSO2ZSND"] + ",'" + dr["AvgNOX"] + "'," + dr["DischargeNOX"] + ",'" + dr["AvgNOXZSND"] + "'," + dr["DischargeNOXZSND"] + ",'" + dr["AvgSMOKE"] + "'," + dr["DischargeSMOKE"] + ",'" + dr["AvgSMOKEZSND"] + "'," + dr["DischargeSMOKEZSND"] + "," + dr["AvgO2"] + "," + dr["AvgFlow"] + "," + dr["SumFlow"] + "," + dr["FYear"] + "," + dr["FMonth"] + ",'" + dr["FDate"] + "') ");
k++;
}
if (!db.ExecDataBySqls(strSqls))
{
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败！", "软件提示");
return;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("数据导入成功", "软件提示");
}
private void getData()
{
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@FBoatID", SqlDbType.Int); //船次ID
param1.Value = this.cobFBoatID.SelectedValue;
SqlParameter param2 = new SqlParameter("@FBoatName", SqlDbType.VarChar); //船次名称
param2.Value = this.cobFBoatID.Text;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param1);
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
DataTable dt01 = k3db.GetDataTable("sp_YZ_JXKHSelectByProject_czq", inputParameters2);
bdsMaster.DataSource = dt01;
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void getData() //查询数据
{
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@FSubContractNumber", txtSubContractNumber.Text.Trim()); //副合同号
SqlParameter param2 = new SqlParameter("@FCustomer", txtCustomer.Text.Trim()); //客户名称
SqlParameter param3 = new SqlParameter("@FItem", txtItem.Text.Trim()); //商品名称
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param1);
parameters2.Add(param2);
parameters2.Add(param3);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
DataTable dt01 = k3db.GetDataTable("sp_YZ_SubContractSelect_czq", inputParameters2);
bdsMaster.DataSource = dt01;
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void btnJS_Click(object sender, EventArgs e)
{
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在计算，请稍候......");
string datestr = "";
datestr = this.dateTimePicker1.Value.ToShortDateString();
SqlParameter param2 = new SqlParameter("@date", SqlDbType.VarChar);
param2.Value = datestr;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
db.GetProcRow("spk3_2_BTSKBM", inputParameters2);
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("计算失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
string sSQL = string.Empty;
//新做法，调用存储过程
//sSQL += " SELECT 市场,g.FNumber K3代码,g.FName 客户名称,前日累欠 ,礼券,现金收款, ";
//sSQL += " 银行存款,[余额(不含当天销售)], ";
//sSQL += " 重量1 一级白条重量, 金额1 一级白条金额, ";
//sSQL += " 重量2 二级白条重量, 金额2 二级白条金额, ";
//sSQL += " 重量3 三级白条重量, 金额3 三级白条金额, ";
//sSQL += " 重量4 四级白条重量, 金额4 四级白条金额, ";
//sSQL += " 重量5 五级白条重量, 金额5 五级白条金额, ";
//sSQL += " 折让金额,当日应付, ";
//sSQL += " p.FSumFamout 次日配送金额, ";
//sSQL += " b.FXYMoney 信用额度, ";
//sSQL += " (ISNULL(当日应付,0)- ISNULL(b.FXYMoney,0))+ ISNULL(p.FSumFamout,0) 余额 ";
//sSQL += " FROM BTSKBM M ";
//sSQL += " LEFT JOIN AIS_YXSP2.dbo.t_Organization g ON m.内码=g.FItemID ";
//sSQL += " LEFT JOIN AIS_YXSP2.dbo.t_SPXYBase b ON m.内码=b.FInterID ";
//sSQL += " LEFT JOIN ";
//sSQL += " ( ";
//sSQL += " SELECT fcurID,FSumFamout FROM AIS_YXSP2.dbo.yx_order WHERE Fpdate='" + Convert.ToDateTime(this.dateTimePicker1.Text).AddDays(1) + "' ";
//sSQL += " ) p ON m.内码=p.fcurID ";
//sSQL += " WHERE fdate='"+Convert.ToDateTime(this.dateTimePicker1.Text)+"'";
//sSQL += " order by (ISNULL(当日应付,0)- ISNULL(b.FXYMoney,0))+ ISNULL(p.FSumFamout,0)";
//dt = db.GetDataTable(sSQL, "exceltab");
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
SqlParameter param1 = new SqlParameter("@fdate", SqlDbType.VarChar);
param1.Value = this.dateTimePicker1.Text;
parameters.Add(param1);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
dt = SqlHelper.ExecuteDataTableByProduce(db.Conn.ConnectionString, "sp_xysp_btps_czq", inputParameters);
bdsMaster.DataSource = dt;
}
private void btnSelect_Click(object sender, EventArgs e)
{
//select
if (this.txtYear.Text == "" || this.txtMonth.Text == "")
{
MessageBox.Show("请输入查询年份和期间！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try { int.Parse(this.txtYear.Text.Trim()); int.Parse(this.txtMonth.Text.Trim()); }
catch
{
MessageBox.Show("输入非数字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
year = this.txtYear.Text.Trim();;
month = this.txtMonth.Text.Trim();
try
{
fcur = this.txtFcur.Text.Trim();
}
catch
{
fcur = "";
}
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@year", SqlDbType.VarChar);
param1.Value = this.txtYear.Text.Trim();
SqlParameter param2 = new SqlParameter("@month", SqlDbType.VarChar);
param2.Value = this.txtMonth.Text.Trim();
SqlParameter param3 = new SqlParameter("@fcur", SqlDbType.VarChar);
param3.Value = fcur;
SqlParameter param4 = new SqlParameter("@isALL", SqlDbType.Int);
if (this.radioButton1.Checked == true)//
{
param4.Value = 1;
}
else
{
param4.Value = 0;
}
//片区
SqlParameter param5 = new SqlParameter("@pq", SqlDbType.VarChar);
param5.Value = this.txtPq.Text.Trim();
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
parameters.Add(param3);
parameters.Add(param4);
parameters.Add(param5);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
dt = k3db.GetDataTable("sp_DzpYsTJ_test", inputParameters);
this.dataGridView1.DataSource = dt;
this.dataGridView1.Columns[0].ReadOnly = false;
//this.dataGridView1.Columns[0].Visible = false;
WaitFormService.CloseWaitForm();
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
}
private void btnInput_Click(object sender, EventArgs e)
{
//Thread thdSub = new Thread(new ThreadStart(ThreadFun));
// thdSub.Start();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
List <string> strSqls = new List <string>();
if (this.dataGridView1.RowCount == 0)
{
return;
}
strSqls.Add(" DELETE k3_2inSalelibrary WHERE FNAME='" + PropertyClass.OperatorName + "'");
string str;
str = "";
DataRow dr = null;
int k = 0;
for (int i = 0; i < dttResult.Rows.Count; i++)
{
dr = dttResult.Rows[i];
strSqls.Add(" INSERT INTO dbo.k3_2inSalelibrary(Fbillno,Fdate,Fpax,FNumberC,FNumberM,FWeight,Frank,FNAME,FSTOCK,FTS,FName1) VALUES ( '" + dr["单号"].ToString().Trim() + "','" + dr["称重时间"].ToString().Trim()
+ "','" + dr["批发行"].ToString().Trim() + "','" + dr["客户代码"].ToString().Trim() + "','" + dr["物料代码"].ToString().Trim() + "'," + dr["重量"].ToString().Trim() + ",'" + dr["等级"].ToString().Trim() + "','" + PropertyClass.OperatorName + "','" + dr["仓库"].ToString().Trim() + "'," + dr["头数"].ToString().Trim() + ",'" + dr["市场"].ToString().Trim() + "') ");
str = str + " INSERT INTO dbo.k3_2inSalelibrary(Fbillno,Fdate,Fpax,FNumberC,FNumberM,FWeight,Frank,FNAME,FSTOCK,FTS,FName1) VALUES ( '" + dr["单号"].ToString().Trim() + "','" + dr["称重时间"].ToString().Trim()
+ "','" + dr["批发行"].ToString().Trim() + "','" + dr["客户代码"].ToString().Trim() + "','" + dr["物料代码"].ToString().Trim() + "'," + dr["重量"].ToString().Trim() + ",'" + dr["等级"].ToString().Trim() + "','" + PropertyClass.OperatorName + "','" + dr["仓库"].ToString().Trim() + "'," + dr["头数"].ToString().Trim() + ",'" + dr["市场"].ToString().Trim() + "') ";
k++;
}
if (!db.ExecDataBySqls(strSqls))
{
this.toolStripStatusLabel1.Text = " 读取表格数据失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败！", "软件提示");
return;
}
//MessageBox.Show("读取成功！", "软件提示");
this.toolStripStatusLabel1.Text = " 开始检查数据......";
SqlParameter param = new SqlParameter("@FNAME", SqlDbType.VarChar);
param.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
//存储过程
DataRow drc = null;
drc = db.GetDataTable("sp_k3_2checkinSalelibrary", inputParameters).Rows[0];
if (drc["isok"].ToString() == "-1")
{
this.toolStripStatusLabel1.Text = " 表格数据检查失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败:" + drc["msg"].ToString(), "软件提示");
return;
}
//return; //先直接返回。。。
this.toolStripStatusLabel1.Text = " 开始写入YXK3FZ......";
int w = k / 50 + 1;//由于导入记录太多会超时 改为每次导入50条
int f = 1;
while (f <= w)
{
DataBase db2 = new DataBase(PropertyClass.con_ry);
SqlParameter param2 = new SqlParameter("@username", SqlDbType.VarChar);
param2.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
db2.GetProcRow("sp_k3_2insertSalelibrary", inputParameters2);
this.toolStripStatusLabel1.Text = " 表格数据导入前" + (f * 50).ToString() + "条记录的数据成功!";
// SetFormCaption
WaitFormService.SetWaitFormCaption("正在导入" + (f * 50).ToString() + "条记录后面的数据！");
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = " 表格数据导入失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("导入K3失败!" + ex.ToString(), "软件提示");
return;
}
f++;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("成功导入K3!", "软件提示");
}
private void btnSelect_Click(object sender, EventArgs e)
{
if (this.txtYear.Text == "" || this.txtMonth.Text == "")
{
MessageBox.Show("请输入查询开始年份和期间！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (this.txtYear1.Text == "" || this.txtMonth1.Text == "")
{
MessageBox.Show("请输入查询结束年份和期间！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try { int.Parse(this.txtYear.Text.Trim()); int.Parse(this.txtMonth.Text.Trim()); }
catch
{
MessageBox.Show("输入非数字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try { int.Parse(this.txtYear1.Text.Trim()); int.Parse(this.txtMonth1.Text.Trim()); }
catch
{
MessageBox.Show("输入非数字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (int.Parse(this.txtYear1.Text) * 12 + int.Parse(this.txtMonth1.Text) < int.Parse(this.txtYear.Text) * 12 + int.Parse(this.txtMonth.Text))
{
MessageBox.Show("开始期间必须大于结束期间！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
year = this.txtYear.Text.Trim();;
month = this.txtMonth.Text.Trim();
year1 = this.txtYear1.Text.Trim();
month1 = this.txtMonth1.Text.Trim();
try
{
fcur = this.txtFcur.Text.Trim();
}
catch
{
fcur = "";
}
//@year_begin VARCHAR(10) ,
//@month_begin VARCHAR(10) ,
//@year_end VARCHAR(10) , --年月份
//@month_end VARCHAR(10) , --年月份
//@fcur VARCHAR(100) ,
//@isALL INT ,
//@pq VARCHAR(100)
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@year_begin", SqlDbType.VarChar);
param1.Value = this.txtYear.Text.Trim();
SqlParameter param2 = new SqlParameter("@month_begin", SqlDbType.VarChar);
param2.Value = this.txtMonth.Text.Trim();
SqlParameter param3 = new SqlParameter("@year_end", SqlDbType.VarChar);
param3.Value = this.txtYear1.Text.Trim();
SqlParameter param4 = new SqlParameter("@month_end", SqlDbType.VarChar);
param4.Value = this.txtMonth1.Text.Trim();
SqlParameter param5 = new SqlParameter("@fcur", SqlDbType.VarChar);
param5.Value = fcur;
SqlParameter param6 = new SqlParameter("@isALL", SqlDbType.Int);
if (this.radioButton1.Checked == true)//
{
param6.Value = 1;
}
else
{
param6.Value = 0;
}
//片区
SqlParameter param7 = new SqlParameter("@pq", SqlDbType.VarChar);
param7.Value = this.txtPq.Text.Trim();
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
parameters.Add(param3);
parameters.Add(param4);
parameters.Add(param5);
parameters.Add(param6);
parameters.Add(param7);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
//if (DB_ID == 4 && Convert.ToInt32(year) == 2018 && Convert.ToInt32(month) == 1)
//{
// dt = k3db.GetDataTable("sp_DzpYsTJ_ts201801", inputParameters);
//}
//else
{
dt = k3db.GetDataTable("sp_DzpYsTJ_qiu", inputParameters);
}
this.dataGridView1.DataSource = dt;
this.dataGridView1.Columns[0].Visible = false;
WaitFormService.CloseWaitForm();
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
}
private void btnExcel_Click(object sender, EventArgs e)
{//导至excel
if (dt.Rows.Count == 0)
{
return;
}
string localFilePath = String.Empty;
//设置文件类型
//saveFileDialog1.Filter = " xls files(*.xls)|*.txt|All files(*.*)|*.*";
//设置文件名称：
saveFileDialog1.FileName = this.textBox1.Text.Trim() + "-" + DateTime.Now.ToString("yyyyMMdd") + "-" + "白条批发销售明细表.xls";
//点了保存按钮进入
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
try
{
//获得文件路径
localFilePath = saveFileDialog1.FileName.ToString();
string wordPath = Application.StartupPath + "\\btsk.xls"; //定义模板的路径
Excel.Application app = new Excel.Application(); //添加一个 Excle应用对象
//打开工作簿，可见很多参数，第一个就是我们模板的路径
Excel._Workbook wbook = app.Workbooks.Open(wordPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel._Worksheet oSheet = (Excel._Worksheet)wbook.Worksheets[1];//创建一张sheet表
int excel_cur = 2;
oSheet.Cells[excel_cur, 2] = dt.Rows[0]["收银员"].ToString();
oSheet.Cells[excel_cur, 4] = dt.Rows[0]["fdate"].ToString();
excel_cur = 6;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
oSheet.Cells[excel_cur, 1] = dt.Rows[i]["市场"].ToString();
oSheet.Cells[excel_cur, 2] = dt.Rows[i]["K3代码"].ToString();
oSheet.Cells[excel_cur, 3] = dt.Rows[i]["客户名称"].ToString();
oSheet.Cells[excel_cur, 4] = dt.Rows[i]["前日累欠"].ToString();
oSheet.Cells[excel_cur, 5] = dt.Rows[i]["礼券"].ToString();
oSheet.Cells[excel_cur, 6] = dt.Rows[i]["现金收款"].ToString();
oSheet.Cells[excel_cur, 7] = dt.Rows[i]["银行存款"].ToString();
oSheet.Cells[excel_cur, 8] = dt.Rows[i]["余额(不含当天销售)"].ToString();
oSheet.Cells[excel_cur, 9] = dt.Rows[i]["重量1"].ToString();
oSheet.Cells[excel_cur, 10] = dt.Rows[i]["金额1"].ToString();
oSheet.Cells[excel_cur, 11] = dt.Rows[i]["重量2"].ToString();
oSheet.Cells[excel_cur, 12] = dt.Rows[i]["金额2"].ToString();
oSheet.Cells[excel_cur, 13] = dt.Rows[i]["重量3"].ToString();
oSheet.Cells[excel_cur, 14] = dt.Rows[i]["金额3"].ToString();
oSheet.Cells[excel_cur, 15] = dt.Rows[i]["重量4"].ToString();
oSheet.Cells[excel_cur, 16] = dt.Rows[i]["金额4"].ToString();
oSheet.Cells[excel_cur, 17] = dt.Rows[i]["重量5"].ToString();
oSheet.Cells[excel_cur, 18] = dt.Rows[i]["金额5"].ToString();
oSheet.Cells[excel_cur, 19] = dt.Rows[i]["折让金额"].ToString();
oSheet.Cells[excel_cur, 20] = dt.Rows[i]["当日应付"].ToString();
oSheet.Cells[excel_cur, 21] = dt.Rows[i]["次日实收现金"].ToString();
oSheet.Cells[excel_cur, 66] = dt.Rows[i]["累计余额"].ToString();
excel_cur++;
}
}
app.Application.DisplayAlerts = false;
oSheet.SaveAs(localFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//文件保存
//打开后就要关闭。O(∩_∩)O~
app.Workbooks.Close();
//同样不要忘记结束进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
GC.Collect();//强制对所有代进行即时垃圾回收
WaitFormService.CloseWaitForm();
MessageBox.Show("导出完成!", "软件提示");
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("导出失败!" + ex.ToString(), "软件提示");
return;
}
}
}
private void button4_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
// ToExcel2(dataGridView1, saveFileDialog);
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
System.Data.DataTable Table = ds.Tables[0];
string ExcelFilePath = saveFileDialog.FileName;
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
try
{
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
WaitFormService.CloseWaitForm();
throw exp;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("导出完成!", "软件提示");
}
catch
{
WaitFormService.CloseWaitForm();
MessageBox.Show("导出失败!", "软件提示");
}
}
private void button3_Click(object sender, EventArgs e)
{
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
List <string> strSqls = new List <string>();
if (this.dataGridView1.RowCount == 0)
{
return;
}
string sSQL = string.Empty;
//清空临时数据
strSqls.Add(" DELETE yx_rs_ysprice_CHECK WHERE FuserName='******'");
strSqls.Add(" DELETE yx_rs_DayHeadNum_Check WHERE FuserName='******'");
sSQL += " DELETE yx_rs_ysprice_CHECK WHERE FuserName='******'";
sSQL += " DELETE yx_rs_DayHeadNum_Check WHERE FuserName='******'";
DataRow dr = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
dr = ds.Tables[0].Rows[i];
strSqls.Add(" INSERT INTO yx_rs_ysprice_CHECK(Fnumber,Fprice,FDATE,FuserName) VALUES ( '" + dr["编码"].ToString() + "'," + dr["成本单价"].ToString() + ",'" + dr["日期"].ToString() + "','" + PropertyClass.OperatorName + "') ");
sSQL += " INSERT INTO yx_rs_ysprice_CHECK(Fnumber,Fprice,FDATE,FuserName) VALUES ( '" + dr["编码"].ToString() + "'," + dr["成本单价"].ToString() + ",'" + dr["日期"].ToString() + "','" + PropertyClass.OperatorName + "') ";
}
//处理当天屠宰头数
for (int i = 0; i < dsDayHeadNum.Tables[0].Rows.Count; i++)
{
dr = dsDayHeadNum.Tables[0].Rows[i];
strSqls.Add(" INSERT INTO yx_rs_DayHeadNum_Check(FDATE,FDayHeadNum,FuserName) VALUES ( '" + dr["日期"].ToString() + "'," + dr["当天屠宰头数"].ToString() + ",'" + PropertyClass.OperatorName + "') ");
sSQL += " INSERT INTO yx_rs_DayHeadNum_Check(FDATE,FDayHeadNum,FuserName) VALUES ( '" + dr["日期"].ToString() + "'," + dr["当天屠宰头数"].ToString() + ",'" + PropertyClass.OperatorName + "')";
}
if (!db.ExecDataBySqls(strSqls))
{
this.toolStripStatusLabel1.Text = " 读取表格数据失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败！", "软件提示");
return;
}
//MessageBox.Show("读取成功！", "软件提示");
this.toolStripStatusLabel1.Text = " 开始检查数据......";
SqlParameter param = new SqlParameter("@FuserName", SqlDbType.VarChar);
param.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
//存储过程
DataRow drc = null;
drc = db.GetDataTable("sp_checkToyx_rs_ysprice ", inputParameters).Rows[0];
if (drc["isok"].ToString() == "-1")
{
this.toolStripStatusLabel1.Text = " 表格数据检查失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败:" + drc["msg"].ToString(), "软件提示");
return;
}
this.toolStripStatusLabel1.Text = " 开始写入YXK3FZ......";
SqlParameter param2 = new SqlParameter("@FuserName", SqlDbType.VarChar);
param2.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
db.GetProcRow("sp_insertToyx_rs_ysprice", inputParameters2);
this.toolStripStatusLabel1.Text = " 表格数据导入成功!";
WaitFormService.CloseWaitForm();
MessageBox.Show("成功导入K3!", "软件提示");
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = " 表格数据导入失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("导入K3失败!" + ex.ToString(), "软件提示");
}
}
private void button2_Click(object sender, EventArgs e)
{
CommExcel.ExportExcel("", dataGridView1, true);
return;
SaveFileDialog saveFileDialog = new SaveFileDialog();
// ToExcel2(dataGridView1, saveFileDialog);
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
System.Data.DataTable Table = ds.Tables[0];
string ExcelFilePath = saveFileDialog.FileName;
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
try
{
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
WaitFormService.CloseWaitForm();
throw exp;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("导出完成!", "软件提示");
}
catch
{
WaitFormService.CloseWaitForm();
MessageBox.Show("导出失败!", "软件提示");
}
//////////////////方法2
// SaveFileDialog saveFileDialog = new SaveFileDialog();
//// ToExcel2(dataGridView1, saveFileDialog);
// saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
// saveFileDialog.FilterIndex = 0;
// saveFileDialog.RestoreDirectory = true;
// saveFileDialog.CreatePrompt = true;
// saveFileDialog.Title = "Export Excel File";
// saveFileDialog.ShowDialog();
// if (saveFileDialog.FileName == "")
// return;
// Stream myStream;
// myStream = saveFileDialog.OpenFile();
// StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
// string str = "";
// WaitFormService.CreateWaitForm();
// WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
// try
// {
// //寫dataGridView1表標題
// for (int i = 0; i < dataGridView1.ColumnCount; i++)
// {
// if (i > 0)
// {
// str += "\t";
// }
// str += dataGridView1.Columns[i].HeaderText.Trim();
// }
// sw.WriteLine(str);
// //寫dataGridView1表內容
// for (int j = 0; j < dataGridView1.Rows.Count; j++)
// {
// string tempStr = "";
// for (int k = 0; k < dataGridView1.Columns.Count; k++)
// {
// if (k > 0)
// {
// tempStr += "\t";
// }
// //tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim() ;
// tempStr += dataGridView1.Rows[j].Cells[k].Value;
// //要增加對含數字的文字類，如身份証號
// //.........欠對身份証號處理，以免在EXCEL打開時被自動轉數字
// }
// sw.WriteLine(tempStr);
// }
// sw.Close();
// myStream.Close();
// }
// catch (Exception ex)
// {
// WaitFormService.CloseWaitForm();
// MessageBox.Show("导出失败!" + ex.ToString(), "软件提示");
// }
// finally
// {
// sw.Close();
// myStream.Close();
// }
// WaitFormService.CloseWaitForm();
// MessageBox.Show("导出完成!", "软件提示");
//////////////////方法1
//System.Data.DataTable dt = ds.Tables[0];
//if (dt.Rows.Count == 0)
//{
// return;
//}
//string localFilePath = String.Empty;
////设置文件类型
////saveFileDialog1.Filter = " xls files(*.xls)|*.txt|All files(*.*)|*.*";
////设置文件名称：
//saveFileDialog1.FileName = DateTime.Now.ToString("yyyyMMdd") + "-" + "食品应收帐款表.xls";
////点了保存按钮进入
//if (saveFileDialog1.ShowDialog() == DialogResult.OK)
//{
// WaitFormService.CreateWaitForm();
// WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
// try
// {
// //获得文件路径
// localFilePath = saveFileDialog1.FileName.ToString();
// //string wordPath = Application.StartupPath + "\\btsk.xls"; //定义模板的路径
// Excel.Application app = new Excel.Application();//添加一个 Excle应用对象
// //打开工作簿，可见很多参数，第一个就是我们模板的路径
// //Excel._Workbook wbook = app.Workbooks.Open(null, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// // Excel.Application.Workbooks.Add(true);
// Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
// Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
// Excel._Worksheet oSheet = (Excel._Worksheet)workbook.Worksheets[1];//创建一张sheet表
// int rowIndex=1;
// int colIndex=0;
// foreach(DataColumn col in dt.Columns)
// {
// colIndex++;
// oSheet.Cells[1, colIndex] = col.ColumnName;
// }
// foreach(DataRow row in dt.Rows)
// {
// rowIndex++;
// colIndex=0;
// foreach(DataColumn col in dt.Columns)
// {
// colIndex++;
// oSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
// }
// }
// app.Application.DisplayAlerts = false;
// oSheet.SaveAs(localFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//文件保存
// //打开后就要关闭。O(∩_∩)O~
// app.Workbooks.Close();
// //同样不要忘记结束进程
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
// GC.Collect();//强制对所有代进行即时垃圾回收
// WaitFormService.CloseWaitForm();
// MessageBox.Show("导出完成!", "软件提示");
// }
// catch (Exception ex)
// {
// WaitFormService.CloseWaitForm();
// MessageBox.Show("导出失败!" + ex.ToString(), "软件提示");
// return;
// }
//}
}
static public void cas(string gu, string tstr)
{
WaitFormService.CreateWaitForm("准备开始打印试题……");
string[] ts = tstr.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
string tempdir = Environment.GetEnvironmentVariable("TEMP");
object path; //文件路径变量
string strContent; //文本内容变量
MSWord.Application wordApp; //Word应用程序变量
MSWord.Document wordDoc; //Word文档变量
string dir = "";
path = tempdir + "\\2645\\AMCalTor\\ans\\" + gu + "\\Ans.doc";
dir = gu;
wordApp = new MSWord.ApplicationClass(); //初始化
//如果已存在，则删除
if (File.Exists((string)path))
{
File.Delete((string)path);
}
//由于使用的是COM库，因此有许多变量需要用Missing.Value代替
Object Nothing = Missing.Value;
wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
//图片文件的路径
string filename = "NULL";//@AppDomain.CurrentDomain.BaseDirectory + "1_00";
//要向Word文档中插入图片的位置
object count = 14;
object WdParagraph = Microsoft.Office.Interop.Word.WdUnits.wdParagraph;//换一行;
Object range;
//定义该插入的图片是否为外部链接
Object linkToFile = false; //默认
//定义要插入的图片是否随Word文档一起保存
Object saveWithDocument = true; //默认
//2
//const int chapter = 4;
const int zNoCount = 5;
const int tNoCountL = 1;
int tNoCountR = ts.Count();
bool flag_nexist = false;
bool flag_nfirstParagraph = false;
int ic = 1;
for (int tNoNum = tNoCountL; tNoNum <= tNoCountR; ++tNoNum)
{
int[] array = new int[6] {
0, 1, 2, 3, 4, 5
};
WaitFormService.SetWaitFormCaption("正在打印第" + tNoNum + "题，共" + tNoCountR + "题。");
for (int i = 0; i <= 5; ++i) // 在0~5之间变换
{
int zNoNum = array[i];
flag_nexist = false;
if (false == File.Exists(Environment.CurrentDirectory + "\\Download\\" + ts[tNoNum - 1] + "_" + zNoNum.ToString()))
{
flag_nexist = true;
}
//Console.WriteLine(tempdir + "\\2645\\AMCalTor\\ans\\" + dir + "\\" + tNoNum.ToString() + "_" + zNoNum.ToString() + ".jpg");
//要向Word文档中插入图片的位置
filename = Environment.CurrentDirectory + "\\Download\\" + ts[tNoNum - 1] + "_" + zNoNum.ToString();
wordApp.Selection.MoveDown(ref WdParagraph, ref count, ref Nothing);//移动焦点
if (flag_nfirstParagraph && (zNoNum == 5 || zNoNum == 0))
{
wordApp.Selection.TypeParagraph();//插入段落
}
if (!flag_nfirstParagraph)
{
flag_nfirstParagraph = true;
wordApp.Selection.TypeText("大学数学标准化考试练习题");
wordApp.Selection.MoveDown(ref WdParagraph, ref count, ref Nothing); //移动焦点
wordApp.Selection.TypeParagraph(); //插入段落
}
if (zNoNum == 5)
{
wordApp.Selection.TypeText("解析");
}
else if (zNoNum == 0)
{
wordApp.Selection.TypeText("第" + ic++.ToString() + "题");
}
if (flag_nexist)
{
wordApp.Selection.TypeText("图片载入失败，自己想吧~");
continue;
}
Image pic = Image.FromFile(filename); //strFilePath是该图片的绝对路径
int intWidth = pic.Width; //长度像素值
int intHeight = pic.Height; //高度像素值
wordApp.Selection.MoveDown(ref WdParagraph, ref count, ref Nothing); //移动焦点
wordApp.Selection.TypeParagraph(); //插入段落
range = wordDoc.Paragraphs.Last.Range;
//wordApp.Selection.TypeParagraph();//插入段落
//wordApp.Selection.MoveDown(ref WdParagraph, ref count, ref Nothing);//移动焦点
//使用InlineShapes.AddPicture方法插入图片
wordDoc.InlineShapes.AddPicture(filename, ref linkToFile, ref saveWithDocument, ref range);
float useableWidth = wordDoc.ActiveWindow.Selection.PageSetup.PageWidth
- wordDoc.ActiveWindow.Selection.PageSetup.LeftMargin
- wordDoc.ActiveWindow.Selection.PageSetup.RightMargin;
//Console.WriteLine("{0} {1} {2} {3}"
// , intWidth, wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width
// , intHeight, wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height);
wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height = intHeight * 3 / 4;
wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width = intWidth * 3 / 4;
if (wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width > useableWidth)
{
wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width = useableWidth;
wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height
= useableWidth / intWidth * intHeight;
}
//if (zNoNum == 1)
//{
// float rate = 3.5f;
// while (wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height * rate > useableWidth / 5
// || wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width * rate > useableWidth)
// rate /= 2;
// if (rate > 1)
// {
// wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width *= rate;
// wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height *= rate;
// }
//}
//else if (zNoNum == 5)
//{
// float rate = 400 / wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width;
// wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width *= rate;
// wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height *= rate;
//}
//Console.WriteLine("{0} {1} {2} {3}"
// , intWidth, wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Width
// , intHeight, wordDoc.Application.ActiveDocument.InlineShapes[wordDoc.Application.ActiveDocument.InlineShapes.Count].Height);
}
}
//WdSaveFormat为Word 97文档的保存格式
object format = MSWord.WdSaveFormat.wdFormatDocument97;
//将wordDoc文档对象的内容保存为DOCX文档
wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
//关闭wordDoc文档对象
wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
//关闭wordApp组件对象
wordApp.Quit(ref Nothing, ref Nothing, ref Nothing);
//Console.WriteLine(path + " 创建完毕！");
//Console.ReadKey(true);
}
private void getData(int FYear)
{
DataBase db = new DataBase();
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在查询，请稍候......");
SqlParameter param1 = new SqlParameter("@FYear", SqlDbType.Int);
param1.Value = this.txtFDate1.Text.Trim();
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param1);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
DataTable dt01 = db.GetDataTable("sp_YZYearSelect01", inputParameters2);
DataTable dt02 = db.GetDataTable("sp_YZYearSelect02", inputParameters2);
object G43 = dt01.Rows[0]["FField06"];
DataRow[] dtr = dt02.Select("FID=2");
object O44 = dtr[0]["FField07"];
object K44 = dtr[0]["FField03"];
object N44 = dtr[0]["FField06"];
object oResult = (Convert.ToDecimal(G43) - Convert.ToDecimal(O44)) * Convert.ToDecimal(K44);
dt02.Rows[1]["FField08"] = oResult;
DataTable dt03 = dt01.Copy(); //复制表1结构1000000/24
object M6 = 35000;
string sSQL = "SELECT FBlastCapacity FROM t_YZParameter WHERE FYear='" + txtFDate1.Text.Trim() + "'";
M6 = db.GetSingleObject(sSQL);
object FField01 = Convert.ToDecimal(N44) / Convert.ToDecimal(G43) * 1000000 / 24 / Convert.ToDecimal(M6);
object FField02 = Convert.ToDecimal(N44) / Convert.ToDecimal(G43);
dt03.Rows[0]["FField01"] = Convert.ToDecimal(FField01).ToString("0.00");
dt03.Rows[0]["FField02"] = Convert.ToDecimal(FField02).ToString("0.00");
bdsMaster01.DataSource = dt01;
bdsMaster02.DataSource = dt02;
bdsMaster03.DataSource = dt03;
}
catch (Exception ex)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("查询失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
}
private void downloadChoice(string templ, bool isOffline)
{
try
{
if (isOffline)
{
string[] strArray = templ.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
string[] strArray2 = null;
for (int i = 0; i < strArray.Length; i++)
{
strArray2 = strArray[i].Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
if (!System.IO.Directory.Exists(System.IO.Directory.GetCurrentDirectory() + @"\Download"))
{
// 目录不存在，建立目录
System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + @"\Download");
}
System.IO.File.Copy("ChoiceSource\\" + subject + "\\" + strArray2[0] + "\\" + strArray2[1] + "\\" + strArray[i] + ".zip", "Download\\" + strArray[i] + ".zip", true);
WaitFormService.SetWaitFormCaption(string.Concat(new object[] { "共", strArray.Length, "道选择题，正在下载第", i + 1, "个选择题" }));
ZipHelper.Zip.Extract(Directory.GetCurrentDirectory() + "\\Download\\" + strArray[i] + ".zip", Directory.GetCurrentDirectory() + "\\Download\\", 0x400);
}
}
else
{
string[] strArray = templ.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
string[] strArray2 = null;
for (int i = 0; i < strArray.Length; i++)
{
strArray2 = strArray[i].Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
if (!System.IO.Directory.Exists(System.IO.Directory.GetCurrentDirectory() + @"\Download"))
{
// 目录不存在，建立目录
System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + @"\Download");
}
FileStream stream = new FileStream("./" + "Download" + "/" + strArray[i] + ".zip", FileMode.Create);
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(new Uri("ftp://202.118.26.80/ChoiceSource/" + subject + "/" + strArray2[0] + "/" + strArray2[1] + "/" + strArray[i] + ".zip"));
request.Method = "RETR";
request.UseBinary = true;
request.Credentials = new NetworkCredential("LoginName", "Q191KPgC");
request.KeepAlive = false;
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
Stream responseStream = response.GetResponseStream();
long contentLength = response.ContentLength;
int count = 0x800;
byte[] buffer = new byte[count];
for (int si = responseStream.Read(buffer, 0, count); si > 0; si = responseStream.Read(buffer, 0, count))
{
stream.Write(buffer, 0, si);
}
stream.Close();
responseStream.Close();
response.Close();
WaitFormService.SetWaitFormCaption(string.Concat(new object[] { "共", strArray.Length, "道选择题，正在下载第", i + 1, "个选择题" }));
ZipHelper.Zip.Extract(Directory.GetCurrentDirectory() + "\\Download\\" + strArray[i] + ".zip", Directory.GetCurrentDirectory() + "\\Download\\", 0x400);
}
}
WaitFormService.CloseWaitForm();
this.Cursor = Cursors.Default;
}
catch (Exception)
{
MessageBox.Show("下载选择题出错了");
}
}
private void btnInput_Click(object sender, EventArgs e)
{
if (this.dataGridView1.RowCount == 0)
{
return;
}
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
System.Data.DataTable myDT = new System.Data.DataTable();
myDT.Columns.Add("DataTime", Type.GetType("System.DateTime")); //称重时间
myDT.Columns.Add("Level_ID", Type.GetType("System.Int32"));
myDT.Columns.Add("Weight", Type.GetType("System.Decimal")); //重量
myDT.Columns.Add("Remark", Type.GetType("System.String"));
myDT.Columns.Add("Level_Code", Type.GetType("System.Int32")); //等级
myDT.Columns.Add("SubWeight", Type.GetType("System.Decimal")); //扣重
myDT.Columns.Add("IsTransfer", Type.GetType("System.Int32"));
myDT.Columns.Add("ShortCut", Type.GetType("System.String"));
myDT.Columns.Add("Discount", Type.GetType("System.Decimal"));
myDT.Columns.Add("Sequence", Type.GetType("System.Int32")); //序列号
myDT.Columns.Add("IdentifyNum", Type.GetType("System.String")); //刺青号
myDT.Columns.Add("IP", Type.GetType("System.String")); //188.188.2.112
myDT.Columns.Add("Mac", Type.GetType("System.String")); //31366888860116
myDT.Columns.Add("IsExport", Type.GetType("System.Int32")); //1
DataRow dtr = null;
string sBeginTime = dttResult.Rows[0]["称重时间"].ToString().Trim();
string sEndTime = string.Empty;
for (int i = 0; i < dttResult.Rows.Count; i++)
{
dtr = myDT.NewRow();
dtr["DataTime"] = Convert.ToDateTime(dttResult.Rows[i]["称重时间"]);
//dtr["DataTime"] = "2017-11-01";
dtr["Level_ID"] = DBNull.Value;
dtr["Weight"] = Convert.ToDecimal(dttResult.Rows[i]["重量"]);
dtr["Remark"] = DBNull.Value;
dtr["Level_Code"] = dttResult.Rows[i]["修整后等级"];
dtr["SubWeight"] = 0;
dtr["IsTransfer"] = 0;
dtr["ShortCut"] = DBNull.Value;
dtr["Discount"] = DBNull.Value;
dtr["Sequence"] = dttResult.Rows[i]["编号"];
dtr["IdentifyNum"] = dttResult.Rows[i]["刺青号"].ToString().PadLeft(2, '0');
dtr["IP"] = "188.188.2.112";
dtr["Mac"] = "31366888860116";
dtr["IsExport"] = 1;
sEndTime = dttResult.Rows[i]["称重时间"].ToString().Trim();
myDT.Rows.Add(dtr);
}
SqlHelper.ExecuteNonQuery(ZtRyconstring, "DELETE from Weight where DataTime >='" + sBeginTime + "' and DataTime <='" + sEndTime + "'");
SqlBulkCopyLib.SqlBulkCopyByDatatable(ZtRyconstring, "Weight", myDT);
WaitFormService.CloseWaitForm();
MessageBox.Show("数据导入成功!", "软件提示");
}
private void button2_Click(object sender, EventArgs e)
{
Ftype = 0;
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
this.toolStripStatusLabel1.Text = " 正在读取表格数据......";
SqlParameter param1 = new SqlParameter("@BegDate", SqlDbType.VarChar);
param1.Value = this.dateTimePicker1.Value.ToShortDateString();
SqlParameter param2 = new SqlParameter("@EndDate", SqlDbType.VarChar);
param2.Value = this.dateTimePicker2.Value.ToShortDateString();;
SqlParameter param3 = new SqlParameter("@fdepnumber", SqlDbType.VarChar);
param3.Value = this.textBox1.Text.Trim();
SqlParameter param4 = new SqlParameter("@fType", SqlDbType.VarChar);
param4.Value = "1";
if (PropertyClass.OperatorName == "李桂炫") //如果不是李桂炫
{
param4.Value = "0";
}
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
parameters.Add(param3);
parameters.Add(param4);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
ds = db.GetProcDataSet("sp_sel_rsjyb", inputParameters);
this.dataGridView1.DataSource = ds.Tables[0];
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (dataGridView1.Columns[i].HeaderText == "当天屠宰头数" || dataGridView1.Columns[i].HeaderText == "当月屠宰头数")
{
dataGridView1.Columns[i].Visible = false; //隐藏当列
}
}
this.toolStripStatusLabel1.Text = " 读取经营数据完成.";
WaitFormService.CloseWaitForm();
if (PropertyClass.OperatorName != string.Empty) //如果不是李桂炫，不显示回款信息
{
List <string> sDate = new List <string>();
DateTime dt1 = Convert.ToDateTime(dateTimePicker1.Text);
DateTime dt2 = Convert.ToDateTime(dateTimePicker2.Text);
while (dt1 <= dt2)
{
sDate.Add(dt1.ToString("yyyy-MM-dd"));
dt1 = dt1.AddDays(1);
}
string sSQL = string.Empty;
sSQL += " SELECT FNumber,FName FROM dbo.t_Department ";
sSQL += " WHERE 1=1 ";
if (textBox1.Text != string.Empty)
{
sSQL += " And FNumber ='" + textBox1.Text.Trim() + "' ";
}
else
{
sSQL += " And FNumber IN('10.11','10.12','10.13','10.14','10.15','10.16','10.17','10.19','10.20','10.21') ";
}
sSQL += " ORDER BY FNumber ";
DataTable dDepart = dbRY.GetDataTable(sSQL, "A");
DataTable dt = new DataTable(); //自定义表
dt.Columns.Add("日期");
dt.Columns.Add("部门代码");
dt.Columns.Add("部门名称");
foreach (string s in sDate)
{
for (int i = 0; i < dDepart.Rows.Count; i++)
{
DataRow dtr = dt.NewRow();
dtr["日期"] = s;
dtr["部门代码"] = dDepart.Rows[i]["FNumber"];
dtr["部门名称"] = dDepart.Rows[i]["FName"];
dt.Rows.Add(dtr);
}
DataRow dtr2 = dt.NewRow();
dtr2["日期"] = s;
dtr2["部门代码"] = "本日小计";
dtr2["部门名称"] = "本日小计";
dt.Rows.Add(dtr2);
}
NewMethod(dt);
}
}
catch (Exception err)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("操作失败！" + err.ToString());
this.toolStripStatusLabel1.Text = " 读取经营数据失败.";
}
}
/// <summary>
/// 自动导入功能 从报表取数据
/// 1.判断报表有没有对应日期的数据，若没有，提示先计算
/// 2.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAutoImport_Click(object sender, EventArgs e)
{
#region 判断
string sSQL = string.Empty;
string sFindDate = Convert.ToDateTime(dateTimePicker1.Text).ToString("yyyy-MM-dd");
sSQL = " SELECT * FROM t_yxryCost WHERE FFDate='" + sFindDate + "'";
DataTable dttCount = dbRY.GetDataTable(sSQL, "A");
if (dttCount.Rows.Count == 0)
{
MessageBox.Show("日期：" + sFindDate + " 报表数据未生成，无法自动导入");
return;
}
#endregion
#region 处理临时表数据
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption("数据正在处理......");
this.toolStripStatusLabel1.Text = " 正在读取数据......";
//执行读取报表数据
SqlParameter param02 = new SqlParameter("@Fdate", SqlDbType.VarChar);
param02.Value = sFindDate;
SqlParameter param01 = new SqlParameter("@FuserName", SqlDbType.VarChar);
param01.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters01 = new List <SqlParameter>();
parameters01.Add(param02);
parameters01.Add(param01);
SqlParameter[] inputParameters01 = parameters01.ToArray();
DataBase db2 = new DataBase();
try
{
db2.GetProcRow("sp_yxryCostAutoImport_czq", inputParameters01);
this.toolStripStatusLabel1.Text = " 数据读取成功!";
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = " 数据读取失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("数据读取失败！", "软件提示");
return;
}
#endregion
#region 调用原来方法执行
this.toolStripStatusLabel1.Text = " 开始检查数据......";
SqlParameter param = new SqlParameter("@FuserName", SqlDbType.VarChar);
param.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
//存储过程
DataRow drc = null;
drc = db.GetDataTable("sp_checkToyx_rs_ysprice ", inputParameters).Rows[0];
if (drc["isok"].ToString() == "-1")
{
this.toolStripStatusLabel1.Text = " 表格数据检查失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("保存失败:" + drc["msg"].ToString(), "软件提示");
return;
}
this.toolStripStatusLabel1.Text = " 开始写入YXK3FZ......";
SqlParameter param2 = new SqlParameter("@FuserName", SqlDbType.VarChar);
param2.Value = PropertyClass.OperatorName;
//创建泛型
List <SqlParameter> parameters2 = new List <SqlParameter>();
parameters2.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters2 = parameters2.ToArray();
try
{
db.GetProcRow("sp_insertToyx_rs_ysprice", inputParameters2);
this.toolStripStatusLabel1.Text = " 表格数据导入成功!";
WaitFormService.CloseWaitForm();
MessageBox.Show("成功导入K3!", "软件提示");
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = " 表格数据导入失败!";
WaitFormService.CloseWaitForm();
MessageBox.Show("导入K3失败!" + ex.ToString(), "软件提示");
}
#endregion
}