//尾页数据 private void btnShadowe_ItemClick(object sender, ItemClickEventArgs e) { string sql = "select TOP 1 * from " + Program.DataBaseName + "..MD_BillItem" + " order by id desc"; List <SqlParameter> list = new List <SqlParameter>(); int id = Convert.ToInt32(DataAccessUtil.ExecuteScalar(sql, list)); FillDataToViews(id); }
public void AddAndUpdate() { CheckViewDatas(); Company company = new Company() { CompanyCode = txtCode.Text.Trim(), CompanyName1 = txtName.Text.Trim(), CompanyType = Convert.ToInt32(lueType.EditValue), Remark = txtRemark.Text.Trim() }; if (btnAdd.Text == "添加") { string sql = "insert into " + Program.DataBaseName + "..MD_Company(CompanyCode,CompanyName,CompanyType,Remark) values(@code,@name,@type,@re)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", company.CompanyCode), new SqlParameter("@name", company.CompanyName1), new SqlParameter("@type", company.CompanyType), new SqlParameter("@re", company.Remark), }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { addCompanyEvent(); this.Close(); } else { throw new ApplicationException("添加失败"); } } else { //修改 string sql = "update " + Program.DataBaseName + "..MD_Company set CompanyCode=@code,CompanyName=@name,CompanyType=@type,Remark=@re" + " where id=" + Tag; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", company.CompanyCode), new SqlParameter("@name", company.CompanyName1), new SqlParameter("@type", company.CompanyType), new SqlParameter("@re", company.Remark), }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { addCompanyEvent(); this.Close(); } else { throw new ApplicationException("修改失败"); } } }
private void FillData(int id) { string sql = "select * from " + Program.DataBaseName + "..MD_Storehouse where Actived=1 and id=" + id; List <SqlParameter> list = new List <SqlParameter>(); DataRow row = DataAccessUtil.ExecuteDataTable(sql, list).Rows[0]; txtCode.Text = row["StorehouseCode"].ToString(); txtName.Text = row["StorehouseName"].ToString(); txtRemark.Text = row["Remark"].ToString(); }
private void FillDatas() { string sql = "select * from " + Program.DataBaseName + "..MD_Company where id=" + Tag; List <SqlParameter> list = new List <SqlParameter>(); DataRow row = DataAccessUtil.ExecuteDataTable(sql, list).Rows[0]; txtCode.Text = row["CompanyCode"].ToString(); txtName.Text = row["CompanyName"].ToString(); lueType.EditValue = row["CompanyType"]; txtRemark.Text = row["Remark"].ToString(); }
/// <summary> /// 删除往来单位 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void barLargeButtonItem5_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { //获得选中的第一行的下标 int selectRow = gv.GetSelectedRows()[0]; //根据下标选择列值 int id = Convert.ToInt32(gv.GetRowCellValue(selectRow, gv.Columns["Id"])); string sql = "update " + Program.DataBaseName + "..MD_Company set Actived=0 where id=" + id; List <SqlParameter> list = new List <SqlParameter>(); if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { LoadAllCompany(); }//gv.DeleteRow(gv.FocusedRowHandle); }
private void IsContainsToDataBase() { string sql = "select * from " + Program.DataBaseName + "..MD_Storehouse where Actived=1" + " and StorehouseCode=@code and StorehouseName=@name"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", txtCode.Text.Trim()), new SqlParameter("@name", txtName.Text.Trim()) }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { throw new ApplicationException("当前要添加的值在数据库中已经存在.."); } }
private void FillData(int id) { string sql = "select * from " + Program.DataBaseName + "..MD_Goods" + " where id=@id and Actived=1"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@id", id) }; DataRow row = DataAccessUtil.ExecuteDataTable(sql, list).Rows[0]; txtCode.Text = row["GoodsCode"].ToString(); txtName.Text = row["GoodsName"].ToString(); lueGoodsFrom.EditValue = row["GoodsFromId"]; lueGoodsCategory.EditValue = row["GoodsCategoryId"]; }
private void btnNext_ItemClick(object sender, ItemClickEventArgs e) { string sql = "select isnull(min(id),0) from MD_Bill where id >" + _flag + " and BillType_ID=0"; List <SqlParameter> list = new List <SqlParameter>(); object value = DataAccessUtil.ExecuteScalar(sql, list); if (value != null) { int tempId = Convert.ToInt32(value); FillDataToViews(tempId); if (tempId != 0) { _flag = tempId; } } }
private void FillDataToViews(int id) { string sql = "select bi.*,b.* from MD_BillItem as bi inner join MD_Bill as b on bi.Bill_ID=b.id inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID where b.BillType_ID=1and bi.id=" + id; List <SqlParameter> list = new List <SqlParameter>(); try { DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); if (table.Rows.Count <= 0) { MessageBox.Show("老哥,没有数据啦...", "提示!!!", MessageBoxButtons.OK); return; } DataRow row = table.Rows[0]; txtPurOddNumber.Text = row["BillCode"].ToString(); deTime.Text = row["MakeDate"].ToString(); txtMaker.Text = row["Maker"].ToString(); lueCompany.EditValue = Convert.ToInt32(row["Company_ID"]); lueStorehouse.EditValue = Convert.ToInt32(row["Storehouse_ID"]); DataTable dt = new DataTable(); dt.Columns.Add("GoodsCode", typeof(string)); dt.Columns.Add("GoodsName", typeof(string)); dt.Columns.Add("GoodsFromName", typeof(string)); dt.Columns.Add("GoodsCategoryName", typeof(string)); dt.Columns.Add("UnitPrice", typeof(decimal)); dt.Columns.Add("Count", typeof(decimal)); dt.Columns.Add("Total", typeof(decimal)); gridControl1.DataSource = dt; DataRow newRow = dt.NewRow(); dt.Rows.Add(newRow); newRow["GoodsCode"] = row["GoodsCode"]; newRow["GoodsName"] = row["GoodsName"]; //这里给lookUpEdit赋值的时候是需要赋值ID的.....千万记住 newRow["GoodsFromName"] = row["GoodsFrom_ID"]; newRow["GoodsCategoryName"] = row["GoodsCategory_ID"]; newRow["UnitPrice"] = row["UnitPrice"]; newRow["Count"] = row["Count"]; newRow["Total"] = row["Total"]; } catch (Exception e) { ErrorHandler.OnError(e); } }
private void BillDataToDataBase() { string sql = "insert into " + Program.DataBaseName + "..MD_Bill" + "(Storehouse_ID,BillType_ID,Maker,MakeDate,Company_ID,BillCode)" + " values(@sid,@bid,@maker,@md,@cid,@bc)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@sid", lueStorehouse.EditValue), new SqlParameter("@bid", true), //1入库,0出库 new SqlParameter("@maker", txtMaker.Text.Trim()), new SqlParameter("@md", deTime.Text.Trim()), new SqlParameter("@cid", lueCompany.EditValue), new SqlParameter("@bc", txtPurOddNumber.Text.Trim()) }; DataAccessUtil.ExecuteNonQuery(sql, list); }
private void AddToStorehouseDataBase() { string sql = "insert into " + Program.DataBaseName + "..MD_Storehouse(StorehouseCode,StorehouseName,remark) values(@code,@name,@remark)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", txtCode.Text.Trim()), new SqlParameter("@name", txtName.Text.Trim()), new SqlParameter("@remark", txtRemark.Text.Trim()), }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { if (AddStorehouseEvent != null) { AddStorehouseEvent(); } Close(); } }
private void DataInsertToDataBase(string goodsCode, string goodsName, string goodsFromNameId, string goodsCategoryNameId, string unitPrice, string count, string total) { string sql = "insert into " + Program.DataBaseName + "..MD_Bill" + "(Storehouse_ID,BillType_ID,Maker,MakeDate,Company_ID,BillCode)" + " values(@sid,@bid,@maker,@md,@cid,@bc)"; List <SqlParameter> sqlParameters = new List <SqlParameter>() { new SqlParameter("@sid", lueStorehouse.EditValue), new SqlParameter("@bid", false), //1入库,0出库 new SqlParameter("@maker", txtMaker.Text.Trim()), new SqlParameter("@md", deTime.Text.Trim()), new SqlParameter("@cid", lueCompany.EditValue), new SqlParameter("@bc", txtPurOddNumber.Text.Trim()) }; if (DataAccessUtil.ExecuteNonQuery(sql, sqlParameters) > 0) { //首先查询出bill表的最后一条Id int billId = SelectLastIdFromBill(); sql = "insert into " + Program.DataBaseName + "..MD_BillItem" + "(Bill_ID,GoodsCode,GoodsName,GoodsFrom_ID,GoodsCategory_ID,UnitPrice,Count,Total)" + " values(@billId,@goodsCode,@goodsName,@goodsFromNameId,@goodsCategoryNameId,@unitPrice" + ",@count,@total)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@billId", billId), new SqlParameter("@goodsCode", goodsCode), new SqlParameter("@goodsName", goodsName), new SqlParameter("@goodsFromNameId", goodsFromNameId), new SqlParameter("@goodsCategoryNameId", goodsCategoryNameId), new SqlParameter("@unitPrice", unitPrice), new SqlParameter("@count", count), new SqlParameter("@total", total) }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { MessageBox.Show("出库单保存成功...", "提示!!", MessageBoxButtons.OK); ResetViews(); //重置数据 } } }
private void btnUp_ItemClick(object sender, ItemClickEventArgs e) { string sql = "select isnull(max(id),0) from MD_Bill where id <" + _flag + " and BillType_ID=0"; List <SqlParameter> list = new List <SqlParameter>(); int tempId = Convert.ToInt32(DataAccessUtil.ExecuteScalar(sql, list)); if (tempId > 0) { FillDataToViews(tempId); if (tempId != 0) { _flag = tempId; } } else { MessageBox.Show("当前已经是第一条单据了", "提示!!!", MessageBoxButtons.OK); return; } }
private void UpdateStorehouseById() { string sql = "update " + Program.DataBaseName + "..MD_Storehouse set StorehouseCode=@code" + ",StorehouseName=@name,remark=@remark where actived=1 and id=" + Convert.ToInt32(Tag); List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", txtCode.Text.Trim()), new SqlParameter("@name", txtName.Text.Trim()), new SqlParameter("@remark", txtRemark.Text.Trim()), }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { //发出事件 if (UpdateEvent != null) { UpdateEvent(); this.Close(); } } }
private bool SumGoodsCount(string goodsName, string count) { string sql = "select isnull(sum(bi.count),0) from MD_BillItem as bi inner join MD_Bill as b on bi.Bill_ID=b.ID where bi.GoodsName=" + goodsName + " and b.BillType_ID=1"; List <SqlParameter> list = new List <SqlParameter>(); int inCount = Convert.ToInt32(DataAccessUtil.ExecuteScalar(sql, list)); if (inCount > 0) { int i = inCount + Convert.ToInt32(count); sql = "update MD_BillItem set count=" + i + " where GoodsName=" + goodsName; List <SqlParameter> s = new List <SqlParameter>(); DataAccessUtil.ExecuteNonQuery(sql, s); return(true); } else { return(false); } }
private void LoadStorehouseList() { string sql = "select * from " + Program.DataBaseName + "..MD_Storehouse where Actived=1"; List <SqlParameter> list = new List <SqlParameter>(); List <Storehouse> storehouses = new List <Storehouse>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); foreach (DataRow row in table.Rows) { storehouses.Add(new Storehouse() { Id = Convert.ToInt32(row["id"]), StorehouseCode = row["StorehouseCode"].ToString(), StorehouseName = row["StorehouseName"].ToString(), Actived = Convert.ToBoolean(row["Actived"]), Remark = row["Remark"].ToString() }); } gridControl1.DataSource = storehouses; }
private void LoadLueStorehouseData() { string sql = "select * from " + Program.DataBaseName + "..MD_Storehouse where Actived=1"; List <SqlParameter> sqlParameters = new List <SqlParameter>(); List <Storehouse> storehouses = new List <Storehouse>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, sqlParameters); foreach (DataRow row in table.Rows) { storehouses.Add(new Storehouse() { Id = Convert.ToInt32(row["Id"]), StorehouseCode = row["StorehouseCode"].ToString(), StorehouseName = row["StorehouseName"].ToString() }); } lueStorehouse.Properties.DisplayMember = "StorehouseName"; lueStorehouse.Properties.ValueMember = "Id"; lueStorehouse.Properties.DataSource = storehouses; }
private void LoadlueCompanyData() { string sql = "select * from " + Program.DataBaseName + "..MD_Company where Actived=1 and CompanyType=1"; List <SqlParameter> list = new List <SqlParameter>(); List <Company> companies = new List <Company>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); foreach (DataRow row in table.Rows) { companies.Add(new Company() { Id = Convert.ToInt32(row["Id"]), CompanyCode = row["CompanyCode"].ToString(), CompanyName1 = row["CompanyName"].ToString() }); } lueCompany.Properties.DisplayMember = "CompanyName1"; lueCompany.Properties.ValueMember = "Id"; lueCompany.Properties.DataSource = companies; }
private void LoadLueGoodsNameData() { string sql = "select * from " + Program.DataBaseName + "..MD_Goods where Actived=1"; List <SqlParameter> list = new List <SqlParameter>(); List <Goods> goodsNameList = new List <Goods>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); foreach (DataRow row in table.Rows) { goodsNameList.Add(new Goods() { Id = Convert.ToInt32(row["Id"]), GoodsCode = row["GoodsCode"].ToString(), GoodsName = row["GoodsName"].ToString() }); } lueGoodsName.ValueMember = "Id"; lueGoodsName.DisplayMember = "GoodsName"; lueGoodsName.DataSource = goodsNameList; }
//删除 private void btnRemove_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { //删除billItem的Id即可 //获得选中的行 int selectedhandle = gridView1.GetSelectedRows()[0]; //获得某列的值 int biId = Convert.ToInt32(gridView1.GetRowCellValue(selectedhandle, "BiId")); //删除操作 string sql = "delete MD_BillItem where id=" + biId; try { if (DataAccessUtil.ExecuteNonQuery(sql, new List <SqlParameter>()) > 0) { LoadAllDataToList(); } } catch (Exception exception) { ErrorHandler.OnError(exception); } }
private void DeleteStorehouseById() { if (gridView1.FocusedRowHandle < 0) { return; } try { if (_info.InRowCell) { int selectRow = gridView1.GetSelectedRows()[0]; //获得选中的第一行的下标 var id = Convert.ToInt32(gridView1.GetRowCellValue(selectRow, gridView1.Columns["Id"])); //根据下标选择列值 string sql = "Update " + Program.DataBaseName + "..MD_Storehouse set actived=0 where id=" + id; List <SqlParameter> list = new List <SqlParameter>(); if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { LoadStorehouseList(); } } } catch (Exception exception) { ErrorHandler.OnError(exception); } }
private void LoadAllCompany() { string sql = "select * from " + Program.DataBaseName + "..MD_Company where Actived=1"; List <SqlParameter> list = new List <SqlParameter>(); List <Company> companies = new List <Company>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); foreach (DataRow row in table.Rows) { companies.Add(new Company() { Id = Convert.ToInt32(row["id"]), CompanyCode = row["CompanyCode"].ToString(), CompanyName1 = row["CompanyName"].ToString(), CompanyType = Convert.ToInt32(row["CompanyType"]), Actived = Convert.ToBoolean(row["Actived"]), Remark = row["Remark"].ToString() }); } gridControl1.DataSource = companies; }
private void DeleteGoods() { int selectRow = gridView1.GetSelectedRows()[0]; //获得选中的第一行的下标 int id = Convert.ToInt32(gridView1.GetRowCellValue(selectRow, gridView1.Columns["Id"])); //根据下标选择列值 //删除先看入库明细和出库明细是否有此商品的单据,如有先删除明细 string sqls = "select * from " + Program.DataBaseName + "..MD_BillItem where GoodsName=" + id; List <SqlParameter> sqlParameters = new List <SqlParameter>(); if (DataAccessUtil.ExecuteDataTable(sqls, sqlParameters).Rows.Count > 0) { MessageBox.Show("确定要删除吗?\r\n当前要删除的商品在单据中存在,请先删除单据..", "提示", MessageBoxButtons.OK); return; } string sql = "update " + Program.DataBaseName + "..MD_Goods set" + " Actived=0 where id=" + id; List <SqlParameter> list = new List <SqlParameter>(); if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { LoadAllGoods(); } //gridView1.DeleteRow(gridView1.FocusedRowHandle); }
public void formCompanyWhere_SelectCompanyEvent(Company company) { string sql = "select * from " + Program.DataBaseName + "..MD_Company where actived=1"; if (!string.IsNullOrEmpty(company.CompanyCode)) { sql += " and CompanyCode=" + "'" + company.CompanyCode + "'"; } if (!string.IsNullOrEmpty(company.CompanyName1)) { sql += " and CompanyName like" + "'%" + company.CompanyName1 + "%'"; } if (company.CompanyType == 0 || company.CompanyType == 1) { sql += " and CompanyType=" + company.CompanyType; } List <SqlParameter> list = new List <SqlParameter>(); List <Company> companies = new List <Company>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); foreach (DataRow row in table.Rows) { companies.Add(new Company() { Id = Convert.ToInt32(row["id"]), CompanyCode = row["CompanyCode"].ToString(), CompanyName1 = row["CompanyName"].ToString(), CompanyType = Convert.ToInt32(row["CompanyType"]), Remark = row["Remark"].ToString() }); } gridControl1.DataSource = companies; }
private void gridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e) { DataRow row = gridView1.GetFocusedDataRow(); if (null != row) { string unitPrice = row["UnitPrice"].ToString(); string goodsName = row["GoodsName"].ToString(); string count = row["Count"].ToString(); string goodFromName = row["GoodsFromName"].ToString(); string goodsCategoryName = row["GoodsCategoryName"].ToString(); if (!string.IsNullOrEmpty(goodsName) && !string.IsNullOrEmpty(count) && !string.IsNullOrEmpty(goodFromName) && !string.IsNullOrEmpty(goodsCategoryName)) { // string sql = // "select g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,sum(bi.Count) as lastcount from MD_BillItem as bi inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID inner join MD_Bill as b on bi.Bill_ID=b.ID inner join MD_Storehouse as s on b.Storehouse_ID=s.ID where b.BillType_ID=1 and bi.GoodsName=" + goodsName + " and bi.GoodsFrom_ID=" + goodFromName + " and bi.GoodsCategory_ID=" + goodsCategoryName + "group by g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID"; string sql = "select * from MD_Stock where Goods_ID=" + goodsName + " and GoodsFrom_ID=" + goodFromName + " and GoodsCategory_ID=" + goodsCategoryName; List <SqlParameter> list = new List <SqlParameter>(); DataRow row1 = DataAccessUtil.ExecuteDataTable(sql, list).Rows[0]; if (Convert.ToDecimal(row1["count"]) < Convert.ToDecimal(count)) { MessageBox.Show("当前库存中并没有这么多商品,只有" + Convert.ToDecimal(row1["count"]) + "个!!", "提示", MessageBoxButtons.OK); row["Count"] = 0; } } if (!string.IsNullOrEmpty(unitPrice) && !string.IsNullOrEmpty(count)) { row["Total"] = Convert.ToDecimal(unitPrice) * Convert.ToDecimal(row["Count"].ToString()); } } }
private void UpdateGoods() { string sql = "update " + Program.DataBaseName + "..MD_Goods set" + " GoodsCode=@code,GoodsName=@name,GoodsFromId=@fid,GoodsCategoryId=@cid" + " where id=@id and Actived=1"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", txtCode.Text.Trim()), new SqlParameter("@name", txtName.Text.Trim()), new SqlParameter("@fid", lueGoodsFrom.EditValue), new SqlParameter("@cid", lueGoodsCategory.EditValue), new SqlParameter("@id", Convert.ToInt32(Tag)) }; if (DataAccessUtil.ExecuteNonQuery(sql, list) > 0) { SelectAllGoodsesEvent(); this.Close();//修改成功关闭窗口 } else { throw new ApplicationException("修改失败"); } }
private void AddGoods() { string sql = "insert into " + Program.DataBaseName + "..MD_Goods(GoodsCode,GoodsName,GoodsFromId,GoodsCategoryId)" + " values(@code,@name,@from,@category)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", txtCode.Text.Trim()), new SqlParameter("@name", txtName.Text.Trim()), new SqlParameter("@from", lueGoodsFrom.EditValue), new SqlParameter("@category", lueGoodsCategory.EditValue), }; int i = DataAccessUtil.ExecuteNonQuery(sql, list); if (i > 0) { btnCancel_Click(null, null); SelectAllGoodsesEvent(); } else { throw new ApplicationException("添加失败.."); } }
//导出报表 private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { DataTable table = DataAccessUtil.ExecuteDataTable("Select * from MD_Stock ", new List <SqlParameter>()); AExcelHelper.ExportDTtoExcel(table, "导出的表", "C:\\Users\\Administrator\\Desktop\\a.xls"); }
private void LoadStockDataList() { //所有的入库单 //select g.ID as gid,gf.ID as gfid,gc.ID as gcid,g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,sum(bi.Count) as lastcount from MD_BillItem as bi inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID inner join MD_Bill as b on bi.Bill_ID=b.ID inner join MD_Storehouse as s on b.Storehouse_ID=s.ID where b.BillType_ID=1 group by g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,g.ID,gf.ID,gc.ID //所有的出库单 //select g.ID as gid,gf.ID as gfid,gc.ID as gcid,g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,sum(bi.Count) as lastcount from MD_BillItem as bi inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID inner join MD_Bill as b on bi.Bill_ID=b.ID inner join MD_Storehouse as s on b.Storehouse_ID=s.ID where b.BillType_ID=0 group by g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,g.ID,gf.ID,gc.ID string sql = "select g.ID as gid,gf.ID as gfid,gc.ID as gcid,b.Storehouse_ID,g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,sum(bi.Count) as lastcount from MD_BillItem as bi inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID inner join MD_Bill as b on bi.Bill_ID=b.ID inner join MD_Storehouse as s on b.Storehouse_ID=s.ID where b.BillType_ID=1 group by g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,g.ID,gf.ID,gc.ID,b.Storehouse_ID"; List <SqlParameter> sqlParameters = new List <SqlParameter>(); List <StockDetail> stockDetailsIn = new List <StockDetail>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, sqlParameters); foreach (DataRow row in table.Rows) { StockDetail stock = new StockDetail(); stock.GoodsName = row["GoodsName"].ToString(); //名称 stock.GoodsFromName = row["GoodsFromName"].ToString(); //产地 stock.GoodsCategoryName = row["GoodsCategoryName"].ToString(); //品种 stock.StorehouseName = row["StorehouseName"].ToString(); //仓库名称 stock.LastCount = row["lastcount"].ToString(); //每个商品的最终入库数量 stock.GoodsId = Convert.ToInt32(row["gid"]); stock.GoodsFromId = Convert.ToInt32(row["gfid"]); stock.GoodsCategoryId = Convert.ToInt32(row["gcid"]); stock.StorehouseId = Convert.ToInt32(row["Storehouse_ID"]); stockDetailsIn.Add(stock); //所有入库结果 } string outSql = "select g.ID as gid,gf.ID as gfid,gc.ID as gcid,b.Storehouse_ID,g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,sum(bi.Count) as lastcount from MD_BillItem as bi inner join MD_Goods as g on bi.GoodsName=g.ID inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID inner join MD_Bill as b on bi.Bill_ID=b.ID inner join MD_Storehouse as s on b.Storehouse_ID=s.ID where b.BillType_ID=0 group by g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,s.StorehouseName,b.BillType_ID,g.ID,gf.ID,gc.ID,b.Storehouse_ID"; List <StockDetail> stockDetailsOut = new List <StockDetail>(); DataTable tableOut = DataAccessUtil.ExecuteDataTable(outSql, sqlParameters); foreach (DataRow row in tableOut.Rows) { StockDetail stock = new StockDetail(); stock.GoodsName = row["GoodsName"].ToString(); //名称 stock.GoodsFromName = row["GoodsFromName"].ToString(); //产地 stock.GoodsCategoryName = row["GoodsCategoryName"].ToString(); //品种 stock.StorehouseName = row["StorehouseName"].ToString(); //仓库名称 stock.LastCount = row["lastcount"].ToString(); //每个商品的最终出库数量 stock.GoodsId = Convert.ToInt32(row["gid"]); stock.GoodsFromId = Convert.ToInt32(row["gfid"]); stock.GoodsCategoryId = Convert.ToInt32(row["gcid"]); stock.StorehouseId = Convert.ToInt32(row["Storehouse_ID"]); stockDetailsOut.Add(stock); //所有出库结果 } List <StockDetail> lastShowDetails = new List <StockDetail>(); foreach (StockDetail detailIn in stockDetailsIn) { foreach (StockDetail detailOut in stockDetailsOut) { if (detailIn.GoodsId == detailOut.GoodsId && detailIn.GoodsFromId == detailOut.GoodsFromId && detailIn.GoodsCategoryId == detailOut.GoodsCategoryId) { StockDetail stock = new StockDetail(); stock.GoodsId = detailIn.GoodsId; stock.GoodsFromId = detailIn.GoodsFromId; stock.GoodsCategoryId = detailIn.GoodsCategoryId; stock.StorehouseId = detailIn.StorehouseId; stock.GoodsName = detailIn.GoodsName; stock.GoodsFromName = detailIn.GoodsFromName; stock.GoodsCategoryName = detailIn.GoodsCategoryName; stock.StorehouseName = detailIn.StorehouseName; stock.LastCount = (Convert.ToDecimal(detailIn.LastCount) - Convert.ToDecimal(detailOut.LastCount)).ToString(); lastShowDetails.Add(stock); } else { lastShowDetails.Add(detailIn); } } } gridControl1.DataSource = lastShowDetails; //将库存量录入数据库 foreach (StockDetail detail in lastShowDetails) { string insertSql = "insert into " + Program.DataBaseName + "..MD_Stock(Goods_ID,Storehouse_ID,Count,GoodsFrom_ID,GoodsCategory_ID) values(@goods_ID,@storehouse_ID,@coun,@goodsFrom_ID,@goodsCategory_ID)"; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@goods_ID", detail.GoodsId), new SqlParameter("@storehouse_ID", detail.StorehouseId), new SqlParameter("@coun", Convert.ToDecimal(detail.LastCount)), new SqlParameter("@goodsFrom_ID", detail.GoodsFromId), new SqlParameter("@goodsCategory_ID", detail.GoodsCategoryId), }; try { DataAccessUtil.ExecuteNonQuery(insertSql, list); } catch (Exception e) { ErrorHandler.OnError(e); } } }
private void FillDataToViews(int id) { string sql = string.Empty; if (id == 1) { sql = "select top 1 g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,bi.*,b.* " + "from MD_BillItem as bi inner join MD_Bill as b on bi.Bill_ID=b.id " + "inner join MD_Goods as g on bi.GoodsName=g.ID " + "inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID " + "inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID where b.BillType_ID=0"; } else { sql = "select g.GoodsName,gf.GoodsFromName,gc.GoodsCategoryName,bi.*,b.* " + "from MD_BillItem as bi inner join MD_Bill as b on bi.Bill_ID=b.id " + "inner join MD_Goods as g on bi.GoodsName=g.ID " + "inner join MD_GoodsFrom as gf on bi.GoodsFrom_ID=gf.ID " + "inner join MD_GoodsCategory as gc on bi.GoodsCategory_ID=gc.ID where b.BillType_ID=0 and bi.id=" + id; } List <SqlParameter> list = new List <SqlParameter>(); DataTable table = DataAccessUtil.ExecuteDataTable(sql, list); if (table.Rows.Count <= 0) { MessageBox.Show("老哥,没有数据啦...", "提示!!!", MessageBoxButtons.OK); return; } DataRow row = table.Rows[0]; txtPurOddNumber.Text = row["BillCode"].ToString(); deTime.Text = row["MakeDate"].ToString(); txtMaker.Text = row["Maker"].ToString(); lueCompany.EditValue = Convert.ToInt32(row["Company_ID"]); lueStorehouse.EditValue = Convert.ToInt32(row["Storehouse_ID"]); DataTable dt = new DataTable(); dt.Columns.Add("GoodsCode", typeof(string)); dt.Columns.Add("GoodsName", typeof(string)); dt.Columns.Add("GoodsFromName", typeof(string)); dt.Columns.Add("GoodsCategoryName", typeof(string)); dt.Columns.Add("UnitPrice", typeof(decimal)); dt.Columns.Add("Count", typeof(decimal)); dt.Columns.Add("Total", typeof(decimal)); gridControl1.DataSource = dt; DataRow newRow = dt.NewRow(); dt.Rows.Add(newRow); newRow["GoodsCode"] = row["GoodsCode"]; // newRow["GoodsName"] = row["GoodsName"]; lueGoodsName.NullText = row["GoodsName"].ToString(); // newRow["GoodsFromName"] = row["GoodsFromName"]; lueGoodsFromName.NullText = row["GoodsFromName"].ToString(); // newRow["GoodsCategoryName"] = row["GoodsCategoryName"]; lueGoodsCategoryName.NullText = row["GoodsCategoryName"].ToString(); newRow["UnitPrice"] = row["UnitPrice"]; newRow["Count"] = row["Count"]; newRow["Total"] = row["Total"]; }