页面放2个gridview 第一个汇总得到FNO 第二个PNo
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using QYLCommmon;
namespace UCStore
{
public partial class ucInoutStoreSub : ucClientBase
{
public ucInoutStoreSub()
{
InitializeComponent();
}
//用于标示当前操作状态
private operType mainOperate = operType.noop;
private void ucInoutStoreSub_Load(object sender, EventArgs e)
{
string billno = args["billNo"].ToString();
string opty = args["opty"].ToString();
txtpNo.Focus();
if (isCenter)
{
SqlHelp = new SqlCenterHelper();
combocodename(cbOutCode);//调出仓库应该是自己
}
else
{
SqlHelp = new SqlClientHelper();
combocodename(cbOutCode);//调出仓库应该是所有
}
combocodename(cbInCode);
if (opty == "add" && billno == "")
{
mainOperate = operType.add;
txtbiller.Text = cuser.userName;
txtbilltime.Text = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
}
else
{
cbInCode.Text = opty;
mainOperate = operType.edit;
}
dataBindbill(billno);
dataBindDetails(billno);
}
//根据主表的选中行显示子表信息(1、添加的时候显示跟问题框中一样的款下的货号信息。2删除要处理。3点击主表时显示对应的信息)
public void showpNoDetail(string fNo)
{
//款号为空的时候显示所有。可以用于点合计行
if (fNo == string.Empty)
{
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
dgvr.Visible = true;
return;
}
}
dgvPNoDetails.ClearSelection();
CurrencyManager cm = (CurrencyManager)BindingContext[dgvPNoDetails.DataSource];
cm.SuspendBinding();
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (fNo != "合计:")
{
if (dgvr.Cells["款号"].Value.ToString() != fNo)
{
dgvr.Visible = false;
}
else
{
dgvr.Visible = true;
}
}
else
{
dgvr.Visible = true;
}
}
cm.ResumeBinding();
}
#region 写的添加选择行和隐藏的功能
//处理主表和子表选中(用于增加和修改数量,所有主表数据源重新加载的时候都要重新处理)
public void dealSelect(string fNo, string pNo)
{
foreach (DataGridViewRow dgvr in dgvFNoDetails.Rows)
{
if (dgvr.Cells["款号"].Value.ToString().ToUpper() == fNo.ToUpper())
{
dgvr.Selected = true;
}
else
{
dgvr.Selected = false;
}
}
CurrencyManager cm = (CurrencyManager)BindingContext[dgvPNoDetails.DataSource];
cm.SuspendBinding();
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (dgvr.Cells["款号"].Value.ToString().ToUpper() == fNo && dgvr.Cells["货号"].Value.ToString().ToUpper() == pNo.ToUpper())
{
dgvr.Selected = true;
}
else
{
dgvr.Selected = false;
}
}
cm.ResumeBinding();
}
#endregion
private void btnAdd_Click(object sender, EventArgs e)
{
addSubBytxt();
}
//添加子货号
private void addSubBytxt()
{
try
{
if (!string.IsNullOrEmpty(txtbillNo.Text))
{
if (getStatus("stoInoutStore", "billNo='" + txtbillNo.Text + "'") != 0)
{
MessageBox.Show("当前记录不是填写状态不可添加!", "系统提示", MessageBoxButtons.OK);
return;
}
}
//判断当前货号是否存在不在的话就增加,否则修改
if (string.IsNullOrEmpty(txtpNo.Text) || string.IsNullOrEmpty(txtqty.Text))
{
MessageBox.Show("请检查数据是否填写完整!");
return;
}
//当前货号已经存在了。
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (dgvr.Cells["货号"].Value.ToString().ToUpper() == txtpNo.Text.ToUpper())
{
int qty = Convert.ToInt32(dgvr.Cells["数量"].Value) + Convert.ToInt32(txtqty.Text);
dgvr.Cells["数量"].Value = qty;
dgvmainshow();
showpNoDetail(dgvr.Cells["款号"].Value.ToString());
dealSelect(dgvr.Cells["款号"].Value.ToString(), dgvr.Cells["货号"].Value.ToString());
return;
}
}
//当前输入的货号不存在
DataTable dt = SqlHelp.GetDataTable(CommandType.Text,
@"select t2.fNo as 款号,t2.pNo as 货号,'" + txtqty.Text + @"' as 数量,t2.color as 颜色代码,t2.size as 尺寸代码
,t3.parName as 颜色名称,t4.parName as 尺寸名称
from desProductSub t2
inner join baseParmSub t3 on t3.parValue = t2.color and t3.parType = '颜色'
inner join baseParmSub t4 on t4.parValue = t2.size and t4.parType = '尺码' where t2.pNo= '" + txtpNo.Text + "'");
((DataTable)dgvPNoDetails.DataSource).Rows.Add(dt.Rows[0]["款号"].ToString(), dt.Rows[0]["货号"].ToString(), dt.Rows[0]["数量"].ToString(), dt.Rows[0]["颜色代码"].ToString(),
dt.Rows[0]["尺寸代码"].ToString(), dt.Rows[0]["颜色名称"].ToString(), dt.Rows[0]["尺寸名称"].ToString());
showpNoDetail(dt.Rows[0]["款号"].ToString());
dgvmainshow();
dealSelect(dt.Rows[0]["款号"].ToString(), dt.Rows[0]["货号"].ToString());
}
catch (Exception)
{
MessageBox.Show("请检查数据是否正确!");
}
}
//通过
private void txtPass_Click(object sender, EventArgs e)
{
if (getStatus("stoInoutStore", "billNo='" + txtbillNo.Text + "'") != 0)
{
MessageBox.Show("当前记录不是填写状态不可点通过!", "系统提示", MessageBoxButtons.OK);
return;
}
else
{
string sql = " update stoInoutStore set status = '1',checkTime = GETDATE(),checker = '" + cuser.userName + "' where billNo = '" + txtbillNo.Text.Trim() + "' ";
SqlHelp.ExecuteNonQuery(CommandType.Text, sql);
this.Close();
}
}
//验证数据是否合法,表格中的数据是否填写完整,货号是否存等,方法不完整需完善
private bool isvalid()
{
if (string.IsNullOrEmpty(cbOutCode.Text) && string.IsNullOrEmpty(cbInCode.Text))
{
return false;
}
if (dgvFNoDetails.Rows.Count == 0)
{
return false;
}
return true;
}
//修改调整
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (!isvalid())
{
MessageBox.Show("数据填写不完整请检查!");
return;
}
List<string> sqllist = new List<string>();
switch (mainOperate)
{
//新增
case operType.add:
string billNo = SqlHelp.getMaxBillNo("stoInoutStore");
txtbillNo.Text = billNo;
//添加表头信息
string sqlinsert = "insert into stoInoutStore ([billNo],[outCodeno],[inCodeno],[status],[billTime],[biller],[updateTime],[Editor],[note]) values ('"
+ txtbillNo.Text + "','" + cbOutCode.SelectedValue.ToString() + "','" + cbInCode.SelectedValue.ToString() + "','" + "0" + "'," + "getdate(),'" + cuser.userName + "',getdate(),'" + cuser.userName + "','" + txtnote.Text + "')";
sqllist.Add(sqlinsert);
//添加表体信息
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
string sqlinsertsub = String.Format("insert into stoInoutStoreSub (pNo,aqty,parentNo) values ('{0}','{1}','{2}')", dgvr.Cells["货号"].Value.ToString(), dgvr.Cells["数量"].Value.ToString(), txtbillNo.Text);
sqllist.Add(sqlinsertsub);
}
break;
//修改
case operType.edit:
//update main
string sqlupdatemain = "update stoInoutStore set [outCodeno] = '" + cbOutCode.SelectedValue.ToString() + "',[inCodeno]='" + cbInCode.SelectedValue.ToString() + "',[updateTime]=getdate(),[Editor]='" + cuser.userName
+ "',[note]='" + txtnote.Text + "' where billNo = '" + txtbillNo.Text.Trim() + "'";
sqllist.Add(sqlupdatemain);
//update sub 先删除后修改增加
DataTable dt = SqlHelp.GetDataTable(CommandType.Text, "select * from stoInoutStoreSub where parentNo = '" + txtbillNo.Text + "'");
foreach (DataRow dr in dt.Rows)
{
bool isexist = false;
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (dgvr.Cells["货号"].Value.ToString().ToUpper() == dr["pNo"].ToString().ToUpper())
isexist = true;
}
if (!isexist)
{
sqllist.Add("delete from stoInoutStoreSub where id='" + dr["id"].ToString() + "'");
}
}
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
int id = 0;
foreach (DataRow dr in dt.Rows)
{
if (dgvr.Cells["货号"].Value.ToString().ToUpper() == dr["pNo"].ToString().ToUpper())
id = (int)dr["id"];
}
if (id != 0)
{
sqllist.Add("update stoInoutStoreSub set aqty = '" + dgvr.Cells["数量"].Value.ToString() + "' where id='" + id + "'");
}
else
{
string sqlinsertsub = String.Format("insert into stoInoutStoreSub (pNo,aqty,parentNo) values ('{0}','{1}','{2}')", dgvr.Cells["货号"].Value.ToString(), dgvr.Cells["数量"].Value.ToString(), txtbillNo.Text);
sqllist.Add(sqlinsertsub);
}
}
break;
default:
break;
}
if (sqllist.Count > 0)
{
SqlHelp.ExecuteTransaction(sqllist);
MessageBox.Show("信息保存成功!", "系统提示", MessageBoxButtons.OK);
mainOperate = operType.edit;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw;
}
}
//表头数量绑定
private void dataBindbill(string billno)
{
DataTable dtm = SqlHelp.GetDataTable(CommandType.Text,
@"select c.name as 调出仓库,b.name as 调入仓库,
a.biller as 制单人,a.billTime as 制单时间,a.note as 备注 from stoInoutStore a
inner join gloShop b on a.inCodeno=b.code
inner join gloShop c on a.outCodeno=c.code " + "where billNo='" + billno + "'");
if (dtm.Rows.Count != 0)
{
cbOutCode.Text = dtm.Rows[0]["调出仓库"].ToString();
cbInCode.Text = dtm.Rows[0]["调入仓库"].ToString();
txtbillNo.Text = billno;
txtbilltime.Text = dtm.Rows[0]["制单时间"].ToString();
txtbiller.Text = dtm.Rows[0]["制单人"].ToString();
txtnote.Text = dtm.Rows[0]["备注"].ToString();
}
}
//子表的数据绑定
private void dataBindDetails(string billno)
{
DataTable dt = SqlHelp.GetDataTable(CommandType.Text,
@"select t2.fNo as 款号,t1.pNo as 货号,t1.aqty as 数量,t2.color as 颜色代码,t2.size as 尺寸代码
,t3.parName as 颜色名称,t4.parName as 尺寸名称
from stoInoutStoreSub t1 inner join desProductSub t2 on t1.pNo = t2.pNo
inner join baseParmSub t3 on t3.parValue = t2.color and t3.parType = '颜色'
inner join baseParmSub t4 on t4.parValue = t2.size and t4.parType = '尺码' where parentNo = '" + billno + "'");
dgvPNoDetails.DataSource = dt;
foreach (DataGridViewColumn dgvc in dgvPNoDetails.Columns)
{
if (dgvc.HeaderText == "数量")
{ dgvc.ReadOnly = false; }
else
{
dgvc.ReadOnly = true;
}
}
dgvmainshow();
}
//主表的显示
private void dgvmainshow()
{
int stylecount = 0;
int amount = 0;
Dictionary<string, int> fnoDct = new Dictionary<string, int>();
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (fnoDct.ContainsKey(dgvr.Cells["款号"].Value.ToString()))
{
fnoDct[dgvr.Cells["款号"].Value.ToString()] = Convert.ToInt32(fnoDct[dgvr.Cells["款号"].Value.ToString()]) + Convert.ToInt32(dgvr.Cells["数量"].Value);
amount = amount + Convert.ToInt32(dgvr.Cells["数量"].Value);
}
else
{
fnoDct.Add(dgvr.Cells["款号"].Value.ToString(), Convert.ToInt32(dgvr.Cells["数量"].Value));
amount = amount + Convert.ToInt32(dgvr.Cells["数量"].Value);
stylecount++;
}
}
DataTable dt = new DataTable();
dt.Columns.Add("款号");
dt.Columns.Add("品名");
dt.Columns.Add("数量");
foreach (KeyValuePair<string, int> kv in fnoDct)
{
string fName = SqlHelp.GetValue(CommandType.Text, "select fName from desProduct where fNo = '" + kv.Key + "'").ToString();
dt.Rows.Add(kv.Key, fName, kv.Value.ToString());
}
dt.Rows.Add("合计:", stylecount.ToString() + "款", amount.ToString() + "件");
dgvFNoDetails.DataSource = dt;
txtpNo.Focus();
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
//只能修改数量并重新修改主表显示
private void dgvPNoDetails_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (getStatus("stoInoutStore", "billNo='" + txtbillNo.Text + "'") != 0)
{
MessageBox.Show("当前记录不是填写状态不可修改!", "系统提示", MessageBoxButtons.OK);
return;
}
if (dgvPNoDetails.Columns[e.ColumnIndex].HeaderText == "数量")
{
dgvmainshow();
dealSelect(dgvPNoDetails.CurrentRow.Cells["货号"].Value.ToString(), dgvPNoDetails.CurrentRow.Cells["款号"].ToString());
}
}
private void txtp_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)13)
{
addSubBytxt();
}
}
//删除判断当前是否只有1行显示。如果之后一行删除后显示所有,否则显示该款剩下的几个货号
private void tsdelect_Click(object sender, EventArgs e)
{
if (dgvPNoDetails.SelectedRows.Count == 1)
{
int visibleCount = 0;
string fNo = string.Empty;
string pNo = string.Empty;
foreach (DataGridViewRow dgvr in dgvPNoDetails.Rows)
{
if (dgvr.Visible)
{
visibleCount++;
fNo = dgvr.Cells["款号"].Value.ToString();
pNo = dgvr.Cells["货号"].Value.ToString();
}
}
dgvPNoDetails.Rows.Remove(dgvPNoDetails.SelectedRows[0]);
dgvmainshow();
if (visibleCount > 1)
{
showpNoDetail(fNo);
}
else
{
showpNoDetail(string.Empty);
}
}
else
{
MessageBox.Show("请选择要删除具体的一行!", "系统提示", MessageBoxButtons.OK);
}
}
//点击主表子表联动显示
private void dgvFNoDetails_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dgvFNoDetails.CurrentRow == null)
{
return;
}
showpNoDetail(dgvFNoDetails.CurrentRow.Cells["款号"].Value.ToString());
}
#region//处理行号不用管
private void dgvFNoDetails_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
{
System.Drawing.Rectangle rectangle = new System.Drawing.Rectangle(e.RowBounds.Location.X,
e.RowBounds.Location.Y, dgvFNoDetails.RowHeadersWidth - 4, e.RowBounds.Height);
TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
dgvFNoDetails.RowHeadersDefaultCellStyle.Font,
rectangle,
dgvFNoDetails.RowHeadersDefaultCellStyle.ForeColor,
TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
}
private void dgvPNoDetails_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
{
System.Drawing.Rectangle rectangle = new System.Drawing.Rectangle(e.RowBounds.Location.X,
e.RowBounds.Location.Y, dgvPNoDetails.RowHeadersWidth - 4, e.RowBounds.Height);
TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
dgvPNoDetails.RowHeadersDefaultCellStyle.Font,
rectangle,
dgvPNoDetails.RowHeadersDefaultCellStyle.ForeColor,
TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
}
#endregion
}
}