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