private void InsertDataToBillItem(int billId, string goodsCode, string goodsName, string goodsFromNameId, string goodsCategoryNameId, string unitPrice, string count, string total) { //如果此商品的ID在单据中存在的话就更新当前入库单据的Count即可 //if (SumGoodsCount(goodsName, count)) //{ // MessageBox.Show("入库单保存成功...", "提示!!", MessageBoxButtons.OK); // ResetViews(); // return; //} string 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(); //重置数据 } }
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("修改失败"); } } }
/// <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 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 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 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 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 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 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 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); }
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 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 CheckFormData() { if (gridView1.RowCount <= 0) { return; } for (int i = 0; i <= gridView1.RowCount - 1; i++) { DataRow row = gridView1.GetDataRow(i); string goodsCode = row["GoodsCode"].ToString(); string goodsName = row["GoodsName"].ToString(); string goodsFromNameId = row["GoodsFromName"].ToString(); string goodsCategoryNameId = row["GoodsCategoryName"].ToString(); string unitPrice = row["UnitPrice"].ToString(); string count = row["Count"].ToString(); if (!string.IsNullOrEmpty(unitPrice) && !string.IsNullOrEmpty(count)) { row["Total"] = Convert.ToDecimal(unitPrice) * Convert.ToDecimal(count); } string total = row["Total"].ToString(); CheckDataIsValid(goodsCode, goodsName, goodsFromNameId, goodsCategoryNameId, unitPrice, count, total); //3,进行更改数据库中的数据 var billItemId = Convert.ToInt32(Tag); var billId = DataAccessUtil.ExecuteScalar("Select Bill_ID from MD_BillItem where ID=" + billItemId, new List <SqlParameter>()); string sqlbi = "update bi set bi.GoodsCode=@code, bi.GoodsName=@gnid," + "bi.GoodsFrom_Id=@gfid, bi.GoodsCategory_ID=@gcid, " + "bi.UnitPrice=@up, bi.Count=@cou, bi.Total=@tol " + // "b.Storehouse_ID=@sid, b.BillType_ID=@bid, " + // "b.Maker=@maker, b.MakeDate=@md, b.Company_ID=@cid, b.BillCode=@bc " + "from MD_BillItem as bi inner join MD_Bill as b" + " on bi.Bill_ID=b.ID where bi.id=" + billItemId; string sqlb = "update b set b.Storehouse_ID=@sid, b.BillType_ID=@bid, " + "b.Maker=@maker,b.MakeDate=@md,b.Company_ID=@cid,b.BillCode=@bc" + " from MD_Bill as b where b.id = " + billId; List <SqlParameter> list = new List <SqlParameter>() { new SqlParameter("@code", goodsCode), new SqlParameter("@gnid", goodsName), new SqlParameter("@gfid", goodsFromNameId), new SqlParameter("@gcid", goodsCategoryNameId), new SqlParameter("@up", unitPrice), new SqlParameter("@cou", count), new SqlParameter("@tol", total), }; List <SqlParameter> listP = 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()) }; if (DataAccessUtil.ExecuteNonQuery(sqlbi, list) > 0 && DataAccessUtil.ExecuteNonQuery(sqlb, listP) > 0) { MessageBox.Show("当前修改已经成功..", "提示!!!", MessageBoxButtons.OK); Close(); } } }