private void FrmConstructionRpt_Load(object sender, EventArgs e)
{
var sql =
"SELECT c.name 字段,p.value 说明 FROM sys.syscolumns c JOIN sys.systypes t ON c.xusertype=t.xusertype JOIN sys.sysobjects o ON c.id=o.id LEFT JOIN sys.extended_properties p ON c.id=p.major_id AND c.colid=p.minor_id WHERE o.name='tf_sgdantwo' AND t.name LIKE '%date%'";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
comboBox2.DataSource = ds.Tables[0];
comboBox2.DisplayMember = "说明";
comboBox2.ValueMember = "字段";
comboBox2.SelectedIndex = comboBox2.Items.Count - 1;
checkBox1.Checked = false;
checkBox2.Checked = false;
checkBox3.Checked = false;
if (SqlHelper.UserType == Resources.UT_Input || SqlHelper.UserType == Resources.UT_Admin)
{
checkBox4.Visible = true;
checkBox5.Visible = true;
}
sql = "SELECT distinct cust FROM dzdj.dbo.customer";
ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
txtCust.SpellSearchSource = GetSpellBoxSource(ds.Tables[0]).ToArray();
var acsc = new AutoCompleteStringCollection();
foreach (DataRow dr in ds.Tables[0].Rows)
{
acsc.Add(dr["cust"].ToString());
}
txtCust.AutoCompleteCustomSource = acsc;
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
CtrlUtil.ClearDgv(dataGridView1);
var wheres = new List <string>();
if (!string.IsNullOrEmpty(dtBegin.Text))
{
var time1 = Convert.ToDateTime(dtBegin.Value.Date.ToString("yyyy-MM-dd"));
wheres.Add($" luodanriqi>='{time1}' ");
}
if (!string.IsNullOrEmpty(dtEnd.Text))
{
var dt = DateTime.Parse(dtEnd.Text).AddDays(1);
wheres.Add($" luodanriqi<'{dt}' ");
}
if (txtWONo.Text.Trim().Length > 0)
{
wheres.Add($" shigongdanhao='{txtWONo.Text.Trim()}'");
}
if (txtOrderNo.Text.Trim().Length > 0)
{
wheres.Add($" dingdanhao like '%{txtOrderNo.Text.Trim()}%'");
}
if (ssbCust.Text.Trim().Length > 0)
{
wheres.Add($" kehu like '%{ssbCust.Text.Trim()}%'");
}
if (txtPartNo.Text.Trim().Length > 0)
{
wheres.Add($" chanpintuhao like '%{txtPartNo.Text.Trim()}%'");
}
if (txtPartName.Text.Trim().Length > 0)
{
wheres.Add($" chanpinmingcheng like '%{txtPartName.Text.Trim()}%'");
}
if (txtPageNo.Text.Trim().Length > 0)
{
wheres.Add($" tuzhiyema like '%{txtPageNo.Text.Trim()}%'");
}
var sql =
"SELECT shigongdanhao 工单号,dingdanhao 生产单号,kehu 客户,jiaohuoqi 交货期,tuzhiyema 页码,DXFbianhao DXF," +
"chanpintuhao 零件图号,chanpinmingcheng 零件名称,cailiao 材料,houdu 厚,changdu 长,kuandu 宽,dingdansuliang 下单数量," +
"dantaoyongliang 单套用量,beizu 备注,gongyiliusheng 工艺流程,luodanriqi 下单日期,luodanyuan 录入人," +
"xiugairiqi 修改日期,xiugairen 修改人,CASE WHEN cls_id='T' THEN '停用' ELSE '' END 是否停用,suhu 审核状态 FROM mf_sgdan ";
//判断用户是否选择了条件
if (wheres.Count > 0)
{
var where = string.Join(" AND ", wheres.ToArray());
sql = $"{sql} WHERE {@where} ORDER BY luodanriqi,dingdanhao,shigongdanhao";
}
_ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = _ds.Tables[0];
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}
private void Goiss()
{
const string sql = "SELECT id '序号',name '工序名称'FROM udbz ";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns[0].Width = 100;
dataGridView1.Columns[1].Width = 100;
dataGridView1.Sort(dataGridView1.Columns[0], System.ComponentModel.ListSortDirection.Ascending);
}
private void InitData()
{
const string sql =
"SELECT a.ID,b.Team '班组名称',a.Name '员工' FROM PD_ProcCard_Employee a JOIN dbo.PD_ProcCard_Team b ON b.ID=a.TeamId ";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnectionString("dzdj"), CommandType.Text, sql);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.Columns[0].Width = 120;
dataGridView2.Columns[1].Width = 200;
dataGridView2.Columns[2].Width = 200;
dataGridView2.Sort(dataGridView2.Columns[1], System.ComponentModel.ListSortDirection.Ascending);
}
private void BindData()
{
var sql = $"SELECT Id,Team FROM PD_ProcCard_Team WHERE DeptId={SqlHelper.DeptId}";
comboBox1.Items.Clear();
comboBox1.Items.Add("");
comboBox1.Text = "";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnectionString("dzdj"), CommandType.Text, sql);
comboBox1.DataSource = ds.Tables[0];
comboBox1.DisplayMember = "Team";
comboBox1.ValueMember = "Id";
}
private void FrmConstructionGather_Load(object sender, EventArgs e)
{
const string sql = "SELECT distinct cust FROM dzdj.dbo.customer";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
ssbCust.SpellSearchSource = GetSpellBoxSource(ds.Tables[0]).ToArray();
var acsc = new AutoCompleteStringCollection();
foreach (DataRow dr in ds.Tables[0].Rows)
{
acsc.Add(dr["cust"].ToString());
}
ssbCust.AutoCompleteCustomSource = acsc;
}
private void GetUserInfo()
{
panel1.Visible = false;
panel2.Visible = false;
const string sql = "SELECT lu as '序号',ID as '帐号',pass as '密码',name as '用户姓名',leiq as '用户类型' FROM IDPASS";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns[0].Width = 46;
dataGridView1.Columns[1].Width = 80;
dataGridView1.Columns[2].Width = 80;
dataGridView1.Columns[3].Width = 80;
dataGridView1.Columns[4].Width = 80;
dataGridView1.Sort(dataGridView1.Columns[0], System.ComponentModel.ListSortDirection.Ascending);
}
/// <summary>
/// 工单单号变化
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtWONo_TextChanged(object sender, EventArgs e)
{
if (txtWONo.Text.Length != 7)
{
return;
}
var sql =
"SELECT shigongdanhao 工单号,dingdanhao 生产单号,kehu 客户,jiaohuoqi 交货期,tuzhiyema 页码,DXFbianhao DXF," +
"chanpintuhao 零件图号,chanpinmingcheng 零件名称,cailiao 材料,houdu 厚,changdu 长,kuandu 宽,dingdansuliang 下单数量," +
"dantaoyongliang 单套用量,beizu 备注,gongyiliusheng 工艺流程,luodanriqi 下单日期,luodanyuan 录入人," +
"xiugairiqi 修改日期,xiugairen 修改人,CASE WHEN cls_id='T' THEN '停用' ELSE '' END 是否停用,suhu 审核状态 FROM " +
$"mf_sgdan WHERE shigongdanhao='{txtWONo.Text.Trim()}'";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}
private void textBox4_TextChanged(object sender, EventArgs e)
{
if (textBox3.Text.Length == 4)
{
var sql = "SELECT zling as '工艺卡号',sgdhao as '工单号',ddhao as '生产单号',kehu as '客户'," +
"jhqi as '订单交期',tuhao as '产品图号', name as '产品名称',yema as '页码', cailiao as '材料'," +
"suliang as '订单数量',gxone as '序号',gxname as '工序名称',gxtwo as '加工工序',tiao as '调机时间'," +
"danjian as '单件加工',xuj as '序价',buzu as '补助',wssulia as '完成数量',hujii as '件资合计'," +
"wsbanz as'加工班组',bei3 as '生产员',wsriqi as '完成日期',cjriqi as '指令创日',lururiqi as '录入日期'," +
$"lururen as '录入人',qgriqi as '修改日期',qgren as '修改人' FROM udktr WHERE zling='{textBox4.Text.Trim()}'";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns[0].Width = 65;
dataGridView1.Columns[1].Width = 65;
dataGridView1.Columns[2].Width = 65;
dataGridView1.Columns[3].Width = 65;
dataGridView1.Columns[4].Width = 65;
dataGridView1.Columns[5].Width = 65;
dataGridView1.Columns[6].Width = 65;
dataGridView1.Columns[7].Width = 65;
dataGridView1.Columns[8].Width = 65;
dataGridView1.Columns[9].Width = 65;
dataGridView1.Columns[10].Width = 65;
dataGridView1.Columns[11].Width = 65;
dataGridView1.Columns[12].Width = 65;
dataGridView1.Columns[13].Width = 65;
dataGridView1.Columns[14].Width = 90;
dataGridView1.Columns[15].Width = 90;
dataGridView1.Columns[16].Width = 65;
dataGridView1.Columns[17].Width = 85;
dataGridView1.Columns[18].Width = 65;
dataGridView1.Columns[19].Width = 65;
dataGridView1.Columns[20].Width = 65;
dataGridView1.Columns[21].Width = 65;
dataGridView1.Columns[22].Width = 65;
dataGridView1.Columns[23].Width = 65;
dataGridView1.Columns[24].Width = 65;
dataGridView1.Columns[25].Width = 65;
dataGridView1.Columns[26].Width = 65;
dataGridView1.Sort(dataGridView1.Columns[0], System.ComponentModel.ListSortDirection.Ascending);
//OnRowDataBound = "gvWOContent_RowDataBound";
}
}
/// <summary>
/// 工单号变更事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtWONo_TextChanged(object sender, EventArgs e)
{
if (txtWONo.Text.Length != 7)
{
return;
}
var sql =
"SELECT b.shigongdanhao '工单号',b.dingdanhao '生产单号',b.jiaohuoqi '订单交期',b.kehu '客户',b.tuzhiyema '页码',b.chanpintuhao '产品图号'," +
"a.xuhao '工序号',a.gongxumingcheng '工序名称',a.shengchanyuan '生产班组',ISNULL(a.wanshengsuliang,0) '完成数量',a.jgsu '加工数'," +
"RTRIM(a.xujah) '序价号',a.xujia '序价',a.buzu '补助',a.gongsi '公式类型',ISNULL(a.hezhi,0) '件资合计',a.lururiqi '录入日期'," +
"RTRIM(a.luruyan) '录入人',a.qiugairiqi '修改日期',RTRIM(a.qgren) '修改人',RTRIM(a.suhu) '审核状态',RTRIM(a.suren) '审核人'," +
"(CASE a.Checked WHEN 1 THEN '已核对' ELSE '' END) '核对状态',a.Checker '核对人',a.Chkdate '核对日期' " +
$"FROM dbo.tf_sgdantwo a LEFT JOIN dbo.mf_sgdan b ON a.shigongdanhao=b.shigongdanhao WHERE a.shigongdanhao='{txtWONo.Text.Trim()}'";
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
//OnRowDataBound = "gvWOContent_RowDataBound";
}
private void btnSearch_Click(object sender, EventArgs e)
{
textBox3.Text = "";
textBox4.Text = "";
var wheres = new List <string>();
if (!string.IsNullOrEmpty(dateTimePicker3.Text))
{
wheres.Add($" lururiqi>='{dateTimePicker3.Text}' ");
}
if (!string.IsNullOrEmpty(dateTimePicker4.Text))
{
var dt = new DateTime();
dt = DateTime.Parse(dateTimePicker4.Text).AddDays(1);
wheres.Add($" lururiqi<'{dt}' ");
}
if (DateTime.Compare(dateTimePicker1.Value, dateTimePicker2.Value) < 0)
{
wheres.Add($" cjriqi>='{dateTimePicker1.Text}' ");
var zdt = new DateTime();
zdt = DateTime.Parse(dateTimePicker2.Text).AddDays(1);
wheres.Add($" cjriqi<'{zdt}' ");
}
if (textBox1.Text.Trim().Length > 0)
{
wheres.Add($" ddhao like '%{textBox1.Text.Trim()}%'");
}
if (textBox2.Text.Trim().Length > 0)
{
wheres.Add($" kehu like '%{textBox2.Text.Trim()}%'");
}
if (textBox5.Text.Trim().Length > 0)
{
wheres.Add($" tuhao like '%{textBox5.Text.Trim()}%'");
}
if (textBox6.Text.Trim().Length > 0)
{
wheres.Add($" yema like '%{textBox6.Text.Trim()}%'");
}
if (textBox7.Text.Trim().Length > 0)
{
wheres.Add($" wsbanz like '%{textBox7.Text.Trim()}%'");
}
var sql = "SELECT zling as '工艺卡号',sgdhao as '工单号',ddhao as '生产单号',kehu as '客户'," +
"jhqi as '订单交期',tuhao as '产品图号', name as '产品名称',yema as '页码', cailiao as '材料'," +
"suliang as '订单数量',gxone as '序号',gxname as '工序名称',gxtwo as '加工工序',tiao as '调机时间'," +
"danjian as '单件加工',xuj as '序价',buzu as '补助',wssulia as '完成数量',hujii as '件资合计'," +
"wsbanz as'加工班组',bei3 as '生产员',wsriqi as '完成日期',cjriqi as '指令创日',lururiqi as '录入日期'," +
"lururen as '录入人',qgriqi as '修改日期',qgren as '修改人' FROM udktr";
//判断用户是否选择了条件
if (wheres.Count > 0)
{
var wh = string.Join(" AND ", wheres.ToArray());
sql = $"{sql} WHERE {wh}ORDER BY zling, gxone,gxtwo";
}
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns[0].Width = 65;
dataGridView1.Columns[1].Width = 65;
dataGridView1.Columns[2].Width = 65;
dataGridView1.Columns[3].Width = 65;
dataGridView1.Columns[4].Width = 65;
dataGridView1.Columns[5].Width = 65;
dataGridView1.Columns[6].Width = 65;
dataGridView1.Columns[7].Width = 65;
dataGridView1.Columns[8].Width = 65;
dataGridView1.Columns[9].Width = 65;
dataGridView1.Columns[10].Width = 65;
dataGridView1.Columns[11].Width = 65;
dataGridView1.Columns[12].Width = 65;
dataGridView1.Columns[13].Width = 65;
dataGridView1.Columns[14].Width = 90;
dataGridView1.Columns[15].Width = 90;
dataGridView1.Columns[16].Width = 65;
dataGridView1.Columns[17].Width = 85;
dataGridView1.Columns[18].Width = 65;
dataGridView1.Columns[19].Width = 65;
dataGridView1.Columns[20].Width = 65;
dataGridView1.Columns[21].Width = 65;
dataGridView1.Columns[22].Width = 65;
dataGridView1.Columns[23].Width = 65;
dataGridView1.Columns[24].Width = 65;
dataGridView1.Columns[25].Width = 65;
dataGridView1.Columns[26].Width = 65;
dataGridView1.Sort(dataGridView1.Columns[0], System.ComponentModel.ListSortDirection.Ascending);
}
/// <summary>
/// 根据条件查找
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e) //查询
{
CtrlUtil.ClearDgv(dataGridView1);
var wheres = new List <string>();
string d;
if (!string.IsNullOrEmpty(comboBox2.Text.Trim()))
{
d = comboBox2.SelectedValue.ToString();
}
else
{
MessageBox.Show(@"请选择一个日期选项!");
comboBox2.DroppedDown = true;
return;
}
if (!string.IsNullOrEmpty(dateTimePicker1.Text))
{
wheres.Add($" {d}>='{dateTimePicker1.Text}' ");
}
if (!string.IsNullOrEmpty(dateTimePicker2.Text))
{
var dtp = DateTime.Parse(dateTimePicker2.Text).AddDays(1);
wheres.Add($" {d}<'{dtp}' ");
}
if (txtWONo.Text.Trim().Length > 0)
{
wheres.Add($" a.shigongdanhao like '%{txtWONo.Text.Trim()}%'");
}
if (txtMONo.Text.Trim().Length > 0)
{
wheres.Add($" b.dingdanhao like '%{txtMONo.Text.Trim()}%'");
}
if (txtPartNo.Text.Trim().Length > 0)
{
wheres.Add($" b.chanpintuhao like '%{txtPartNo.Text.Trim()}%'");
}
if (txtTeam.Text.Trim().Length > 0)
{
wheres.Add($" a.shengchanyuan like '%{txtTeam.Text.Trim()}%'");
}
if (txtCust.Text.Trim().Length > 0)
{
wheres.Add($" b.kehu like '%{txtCust.Text.Trim()}%'");
}
if (txtPage.Text.Trim().Length > 0)
{
wheres.Add($" b.tuzhiyema like '%{txtPage.Text.Trim()}%'");
}
if (checkBox3.Checked)
{
wheres.Add(" a.xujah like '%C15%'");
}
if (checkBox2.Checked)
{
wheres.Add(" a.xujah not like '%C15%'");
}
if (SqlHelper.UserType == Resources.UT_Input || SqlHelper.UserType == Resources.UT_Admin)
{
if (checkBox4.Checked)
{
wheres.Add(" a.xujah like '%W11.1%'");
}
else if (checkBox5.Checked)
{
wheres.Add(" a.xujah not like '%W11.1%'");
}
}
if (comboBox1.Text.Trim().Length > 0)
{
if (comboBox1.Text.Trim() == Resources.UT_Audit)
{
wheres.Add(" a.suhu ='审核'");
}
else if (comboBox1.Text.Trim() == "未审核")
{
wheres.Add(" a.suhu is null or a.suhu =''");
}
}
//string sql = "SELECT shigongdanhao as '工单号',ddhao as '生产单号',kehu as '客户',jhqi as '订单交期',"+
// "chanpintuhao as '产品图号', xuhao as '工序号',gongxumingcheng as '工序名称',shengchanyuan as '生产班组',"+
// "wanshengsuliang as'完成数量',jgsu as '加工数',xujah as'序价号',xujia as '序价',buzu as '补助',"+
// "gongsi as '公式类型',hezhi as '件资合计',lururiqi as '录入日期',luruyan as '录入人',"+
// "qiugairiqi as '修改日期',qgren as '修改人',suhu as '审核状态',suren as '审核人' FROM tf_sgdantwo ";
var sql =
"SELECT b.shigongdanhao '工单号',b.dingdanhao '生产单号',b.jiaohuoqi '订单交期',b.kehu '客户',b.tuzhiyema '页码',b.chanpintuhao '产品图号'," +
"a.xuhao '工序号',a.gongxumingcheng '工序名称',a.shengchanyuan '生产班组',ISNULL(a.wanshengsuliang,0) '完成数量',a.jgsu '加工数'," +
"RTRIM(a.xujah) '序价号',a.xujia '序价',a.buzu '补助',a.gongsi '公式类型',ISNULL(a.hezhi,0) '件资合计',a.lururiqi '录入日期'," +
"RTRIM(a.luruyan) '录入人',a.qiugairiqi '修改日期',RTRIM(a.qgren) '修改人',RTRIM(a.suhu) '审核状态',RTRIM(a.suren) '审核人'," +
"(CASE a.Checked WHEN 1 THEN '已核对' ELSE '' END) '核对状态',a.Checker '核对人',a.Chkdate '核对日期' " +
"FROM dbo.tf_sgdantwo a LEFT JOIN dbo.mf_sgdan b ON a.shigongdanhao=b.shigongdanhao ";
//判断用户是否选择了条件
if (wheres.Count > 0)
{
var wh = string.Join(" AND ", wheres.ToArray());
sql = $"{sql} WHERE {wh} ORDER BY a.shigongdanhao,xuhao";
}
var ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}