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