//尾页数据
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"];
}
