string ZtRyconstring = string.Empty; //获取K3账套连接字符串
private void button1_Click(object sender, EventArgs e) //查询
{
string sFDate1 = Convert.ToDateTime(dateTimePicker1.Text).ToString("yyyy-MM-dd");
string sFDate2 = Convert.ToDateTime(dateTimePicker2.Text).ToString("yyyy-MM-dd");
string sSQL = string.Empty;
SqlParameter param1 = new SqlParameter("@BeginDate1", SqlDbType.VarChar);
param1.Value = sFDate1;
SqlParameter param2 = new SqlParameter("@EndDate1", SqlDbType.VarChar);
param2.Value = sFDate2;
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
try
{
ds = k3db.GetProcDataSet("sp_yxryCost_czq_new", inputParameters);
this.dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception err)
{
WaitFormService.CloseWaitForm();
MessageBox.Show("操作失败！" + err.ToString());
}
}
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 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 button1_Click_1(object sender, EventArgs e)
{
string sqlstr1;
string sqlstr2;
DataBase db2 = new DataBase(k3constr);
int LISTCOUNT = listBox1.Lines.Length;
for (int i = 0; i <= LISTCOUNT - 1; i++)
{
string FGroupID = listBox1.Lines[i].ToString();
sqlstr1 = @"exec ICSale_fgc '" + FGroupID + @"'";
try
{
db2.ExecDataBySql(sqlstr1);
}
catch (Exception ex)
{
//WaitFormService.CloseWaitForm();
MessageBox.Show("反钩稽失败!" + ex.ToString(), "软件提示");
return;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("反钩稽成功!", "软件提示");
}
}
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 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 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 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 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();
}
void timer1_Tick(object sender, EventArgs e)
{
Thread th = new Thread(new ThreadStart(this.ExecWaitForm));
th.Start();
string phath = System.Environment.CurrentDirectory;
// var word = new CtrlWinWord()
//{
word.Dock = System.Windows.Forms.DockStyle.Fill;
word.Location = new System.Drawing.Point(0, 0);
word.Name = "文档";
word.TabIndex = 1;
// };
//加载word的完整路径 修改此处
if (File.Exists(_url))
{
word.LoadDocument(_url);
word.Show();
plMain.Controls.Add(word);
}
timer.Enabled = false;
WaitFormService.Close();
}
private void auto()
{
qg_label1.Text = pro3.Substring(0, 4) + "年" + pro3.Substring(4, 2) + "月";
WaitFormService.Show();
WaitFormService.SetText("正在查询工作票信息,请稍候……");
grid_dayworksql.DataSource = dt_daywork;
WaitFormService.Close();
}
/// 生成一个凭证的汇总表
/// </summary>
/// <param name="pro3begin">开始月份</param>
/// <param name="pro3end">结束月份</param>
/// <param name="pzsubid">查询的财务科目ID</param>
/// <param name="monthjytt">为为表示查询一般的业务流程，为1表示是为了扎帐进行查询，这时accountacc的查询凭证信息隐藏模式应该为3</param>
/// <param name="wfilialeid">是“企业”还是“学校”</param>
/// <returns></returns>
private DataTable customercodingsubbillother(string pro3begin, string pro3end, string pzsubid, int monthjytt, string wfilialeid)
{
DataTable dt_subbill = null;
WaitFormService.Show();
WaitFormService.SetText("正在查询凭证信息,请稍候…………");
try
{
DataTable dt;
DataRow dt_row = null;//增加拷贝记录时用到
accountacc.accountacc_one accone = new accountacc.accountacc_one();
string subfialtemp1 = "";
if (monthjytt == 1)
{
subfialtemp1 = "3";
}
else
{
subfialtemp1 = "5";
}
DataTable dt_subbillx1 = accone.accountaccone(pro3begin, pro3end, "", pzsubid, "", "", "", "", subfialtemp1, "", "", "", "1");
dt_subbill = dt_subbillx1.DefaultView.ToTable(true, "ID", "票据号", "凭证号", "附件数", "日期", "月份", "状态ID", "状态", "类型ID", "凭证类型", "属性ID", "属性中文"
, "主管ID", "财务主管", "制单ID", "制单", "审核ID", "审核", "记帐ID", "记帐", "分ID"
, "引用属性", "公司ID", "员工ID", "产品ID"
, "摘要", "科目ID", "反科目ID", "次科目借贷", "借贷方向", "数量");
dt_subbill.Clear();//清除表中的所有记录
DataColumn dc1 = new DataColumn("科目名称", Type.GetType("System.String"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("借方金额", Type.GetType("System.Decimal"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("贷方金额", Type.GetType("System.Decimal"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("排序", Type.GetType("System.Int32"));
dc1.DefaultValue = 1;
dt_subbill.Columns.Add(dc1);
DataRow[] row_1 = dt_subbillx1.Select("借贷方向=" + 1);
for (int i = 0; i < row_1.Count(); i++)
{
dt_row = dt_subbill.NewRow();
dt_subbill.Rows.Add(dt_row);
}
}
catch (Exception ex)
{
ex.errormess();
}
WaitFormService.Close();
return(dt_subbill);
}
private void customerwcodingsub_Shown(object sender, EventArgs e)
{
WaitFormService.Show();
wcoding.wcodingfinance wcoding = new newfeixiang.wcoding.wcodingfinance();
DataTable dt = wcoding.wcodingfinancetreesql4(begin_class.allmax, "1", 1, "");
WaitFormService.Close();
qg_grid_tree1.DataSource = dt;
}
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 ExecWaitForm()
{
try
{
WaitFormService.Show();
}
catch (Exception ex)
{
WaitFormService.Close();
}
}
private void ExecWaitForm()
{
try
{
WaitFormService.Show();
}
catch (Exception ex)
{
Log4NetHelper.WriteErrorLog(ex.Message);
}
}
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("更新单价成功！", "软件提示");
}
}
void timer1_Tick(object sender, EventArgs e)
{
Thread th = new Thread(new ThreadStart(this.ExecWaitForm));
th.Start();
Image image = Image.FromStream(WebRequest.Create(_url).GetResponse().GetResponseStream());
pictureBox1.BackgroundImage = image;
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
label1.Text = "材料:" + _filename;
timer.Enabled = false;
WaitFormService.Close();
}
public void worker_DoWork(object sender, DoWorkEventArgs e)
{
this.Cursor = Cursors.AppStarting;
WaitFormService.CreateWaitForm("正在下载选择题");
if (!this.ixOffline)
{
this.downloadChoice(this.exerciseChoice, false);
}
else
{
this.downloadChoice(this.exerciseChoice, true);
}
}
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 qg_dy_Click(object sender, EventArgs e)
{
try
{
//string dy_title = "我是标题";
//string dy_month = "201612";
Boolean findjytt = false;
Control con_Form = this.Parent;
foreach (Control trol in con_Form.Controls)
{
if (trol is qg_grid || trol is qg_grid_tree)
{
findjytt = true;
//dt = (DataTable)(((qg_grid)trol).DataSource);
TestDelegate d = Test;
//other other = new other();
//string ly = "数据正在导出，请稍候……";
//other.winwait((qg_grid)trol, ly);
WaitFormService.Show();
WaitFormService.SetText("正在做导出Excel的准备,请稍候……");
d.BeginInvoke((DataGridView)trol, dy_title, dy_month, null, null);
//sqlconn.Cs_Datatable.ExportExcel((qg_grid)trol, dy_title, dy_month);
Thread.Sleep(3000);
WaitFormService.Close();
//other.winwait((qg_grid)trol, "");
break;
}
}
if (!findjytt)
{
MessageBox.Show("没有在本表单查询到需要导出的数据表！");
}
}
catch (Exception ex)
{
ex.errormess();
}
}
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();
}
/// </summary>将"科目编码"生成的表进行汇总(来自"wcodingfinancetreesql1"和"wcodingfinancetreesql2")
/// <param name="pro3">月份,暂无用</param>
/// <param name="typeid">表wcodingtype中的ID,是“公司编码”还是“科目编码”</param>
/// <param name="typesubid">次科目ID,是"资产类"还是"负债类"</param>
/// <param name="treeyc">有时是不需要隐藏全部节点的，为0表示不隐藏，为1表示隐藏</param>
/// <param name="wfilialeid">是"企业"还是"学校"</param>
/// <return >
public DataTable wcodingfinancetreesql3(string pro3, string typeid, string typesubid, int treeyc, string wfilialeid)
{
//WaitFormService.Show();
WaitFormService.SetText("正在查询编码数据库，请稍候…………");
DataTable dt_sql3 = null;
try
{
DataTable dt_sql1 = wcodingfinancetreesql1(pro3, typeid, typesubid, wfilialeid);
dt_sql3 = wcodingfinancetreesql2(dt_sql1, treeyc, wfilialeid);
}
catch (Exception ex)
{
throw new Exception("查询数据库出错!");
}
//WaitFormService.Close();
return(dt_sql3);
}
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 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 qg_button1_Click(object sender, EventArgs e)
{
try
{
DataTable dt_all = a_sqlconn.dt_excel.GetDataFromExcelByCom();
if (dt_all == null)
{
return;
}
WaitFormService.Show();
WaitFormService.SetText("正在将图书目录的数据内容格式统一化！");
//将所有内容清除空格，全角转半角
#region
for (int i = 0; i < dt_all.Rows.Count; i++)
{
for (int j = 0; j < dt_all.Columns.Count; j++)
{
//使用正则表达式.你能使用Regex.Replace方法, 它将所有匹配的替换为指定的字符
//.在这个例子中,使用正则表达式匹配符"\s",它将匹配任何空格包含在这个字符串里C#空格, tab字符, 换行符和新行(newline).
dt_all.Rows[i][j] = Regex.Replace(dt_all.Rows[i][j].ToString(), @"\s", "").To_X_DBC();
//最后再将全角转换为半角
}
}
#endregion
//将所有内容清除空格，全角转半角
WaitFormService.Close();
DataColumn dc1 = new DataColumn("删除记号", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_all.Columns.Add(dc1);
#region 除有“ISBN号”的行之前的行
foreach (DataRow dr in dt_all.Rows)
{
bool deletejytt = true;
for (int i = 0; i < dt_all.Columns.Count; i++)
{
if (dr[i].ToString() == "ISBN号")
{
deletejytt = false;
}
}
if (deletejytt)
{
dr["删除记号"] = 1;
}
else
{
break;
}
}
DataRow[] row_update = dt_all.Select("删除记号=1");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
dt_all.Columns.Remove("删除记号");
if (dt_all.Rows.Count <= 0)
{
MessageBox.Show("导入的图书目录表不正常，没有“ISBN号”列，无法导出图书信息！");
return;
}
#endregion 除有“ISBN号”的行之前的行
//将标头整理为有“ISBN号”行的正常标头，同时删除没有记录的column
#region
DataTable dt_temp = new DataTable("Datas");
DataColumn dc = null;
dc = dt_temp.Columns.Add("删除记录", Type.GetType("System.String"));
DataRow dt_NO_1 = dt_all.Rows[0];
//确保“图书目录条目”没有重复
for (int i = 0; i < dt_all.Columns.Count; i++)
{
string nametemp1 = dt_NO_1[i].ToString().Trim();
if (nametemp1.Trim() == "")
{
continue;
}
for (int j = i + 1; j < dt_all.Columns.Count; j++)
{
string nametemp2 = dt_NO_1[j].ToString().Trim();
if (nametemp1.Trim() == nametemp2.Trim())
{
MessageBox.Show("有重复的图书目录条目：“" + nametemp1.Trim() + "”!\n\r 请先确保图书目录条目不重复！");
return;
}
}
}
//确保“图书目录条目”没有重复
//如果ISBN号列正常，则将column的名称正常化，否则将column删除
for (int i = 0; i < dt_all.Columns.Count; i++)
{
if (dt_NO_1[i].ToString().Trim() != "")
{
dt_all.Columns[i].ColumnName = dt_NO_1[i].ToString().Trim();
}
else
{
DataRow dt_temp_newRow = dt_temp.NewRow();
dt_temp_newRow["删除记录"] = dt_all.Columns[i].ColumnName.ToString().Trim();
dt_temp.Rows.Add(dt_temp_newRow);
}
}
foreach (DataRow dr in dt_temp.Rows)
{
string str = dr["删除记录"].ToString().Trim();
dt_all.Columns.Remove(str);
}
dt_all.Rows.RemoveAt(0);
#endregion
//如果图书目录表中的表头多次出现，则删除之
#region
dc1 = new DataColumn("删除记号", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_all.Columns.Add(dc1);
foreach (DataRow dr in dt_all.Rows)
{
if (dr[0].ToString().Trim() == dt_all.Columns[0].ColumnName.ToString().Trim() &&
dr[1].ToString().Trim() == dt_all.Columns[1].ColumnName.ToString().Trim()
)
{
dr["删除记号"] = 1;
}
}
row_update = dt_all.Select("删除记号=1");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
dt_all.Columns.Remove("删除记号");
#endregion
//删除“ISBN号”为空的行
#region
row_update = dt_all.Select("ISBN号='' or (ISBN号 like '%合%' and ISBN号 like '%计%')");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
#endregion
//判断是否有重名的ISBN号 记录
#region
bool cfjytttemp1 = false;//是否有重复的姓名，false为没有重复
string cfstringalltemp1 = "";
int cfcounttemp1 = 0;
var groupByResult = dt_all.Rows.Cast <DataRow>().GroupBy <DataRow, string>(dr => dr["ISBN号"].ToString());
foreach (var rows in groupByResult)
{
if (rows.Count() > 1)
{
//以“姓名”为筛选条件的数据存在多条
cfjytttemp1 = true;
DataTable dttemp = rows.CopyToDataTable <DataRow>();
cfstringalltemp1 = cfstringalltemp1 + "\n\r " + dttemp.Rows[0]["ISBN号"].ToString();
cfcounttemp1 = cfcounttemp1 + 1;
}
}
if (cfjytttemp1)
{
MessageBox.Show("存在 " + cfcounttemp1.ToString() + " 条相同ISBN号的记录!请确保没有重复ISBN号，然后再导出图书目录表！\n\r " + cfstringalltemp1.Trim());
return;
}
#endregion
//判断是否有重名的姓名 记录
qg_grid1.DataSource = dt_all;
qg_grid1.AutoGenerateColumns = true;
}
catch (Exception ex)
{
ex.errormess();
}
}