录入销售单这个模块,可以录入销售单,如果一个客户同时修多个表,有个再录一条按钮为同一个客户录入多条销售单,最后结账如(图1)所示。
(图1)
从界面上可以看到我们这里用到的控件有
控件名称 | 说明 |
日期控件(DateTimePicker) | 控件可以在工具箱直接拖动至窗体,拖至窗体后右击属性可以修改控件的样式和各种属性,还可以编辑事件。 |
下拉框(ComboBox) | |
文本(TextBox) | |
按钮(Button) | |
数据表格(DataGridView) |
数据库表与关系如(图2)所示:
表1: 销售单表(pw_XiaoShouDan)
列名 | 数据类型 | 主键/外键 | 说明 |
XiaoShouDanID | int | 主键 | 销售单ID |
LuRuYuanID | int | 外键 | 员工表,员工ID |
XiaoShouMenDianID | int | 外键 | 备用 |
FuKuanZhuangTaiID | int | 外键 | 属性明细表,付款状态ID |
KeHuID | int | 外键 | 客户表,客户ID |
PinPaiID | int | 外键 | 品牌表,品牌ID |
ZhongBiaoKuanShiID | int | 外键 | 属性明细表,钟表款式ID |
XiaoShouLeiXingID | int | 外键 | 属性明细表,销售类型ID |
XiaoShouFangShiID | int | 外键 | 属性明细表,销售方式ID |
SongHuoFangShiID | int | 外键 | 属性明细表,送货方式ID |
FuKuanFangShiID | int | 外键 | 属性明细表,付款方式ID |
FuKuanLeiXingID | int | 外键 | 属性明细表,付款类型ID |
XiaoShouDanBianHao | nchar(50) |
| 销售单自动编号 |
JieZhangBianHao | nchar(50) |
| 结账单自动编号 |
DiKeHao | nchar(50) |
| 底壳号 |
XiaoShouRiQi | date |
| 销售日期 |
YingShouJinE | decimal(18, 4) |
| 应收金额 |
ShiShouJinE | decimal(18, 4) |
| 实收金额 |
GengXinShiJian | datetime |
| 更新时间 |
BeiZhu | nchar(100) |
| 备注 |
BaoFeiYuanYin | nchar(100) |
| 报废时填写报废原因 |
YouXiaoFou | bit |
| 有效否 |
表2: 销售单明细表(pw_XiaoShouDanMingXi)
列名 | 数据类型 | 主键/外键 | 说明 |
XiaoShouDanMingXiID | int | 主键 | 销售单明细ID |
XiaoShouDanID | int | 外键 | 销售单表,销售单ID |
ChanPinID | int | 外键 | 产品表,产品ID |
CangWeiID | int | 外键 | 仓位表,仓位ID (产品提取处) |
BaoXiuQiID | int | 外键 | 保修期表,保修期ID |
DanJia | decimal(18, 4) |
| 产品销售单价 |
ShuLiang | int |
| 产品数量 |
YiTuiHuoShu | int |
| 已退货数(默认为0) |
YouXiaoFou | bit |
| 有效否 |
GengXinShiJian | datetime |
| 更新时间 |
BeiZhu | nchar(50) |
| 备注 |
表3:库存表(sys_KuCun)
列名 | 数据类型 | 主键/外键 | 说明 |
KuCunID | int | 主键 | 库存ID |
LuRuYuanID | int | 外键 | 录入员ID |
CangWeiID | int | 外键 | 仓位ID |
ChanPinID | int | 外键 | 产品ID |
KuCunShuLiang | int |
| 库存数量 |
GengXinShiJian | datetime |
| 更新时间 |
表4:产品资料表(pw_ChanPinZiLiao)
列名 | 数据类型 | 主键/外键 | 说明 |
ChanPinID | int | 主键 | 产品ID |
LuRuYuanID | int | 外键 | 录入员ID |
CangKuLeiXingID | int | 外键 | 仓库类型ID |
ChanPinLeiXingID | int | 外键 | 产品类型ID |
ChanPinZhongLeiID | int | 外键 | 产品种类ID |
ChanPinPinPaiID | int | 外键 | 产品品牌ID |
DanWeiID | int | 外键 | 单位ID |
ChanPinBianHao | nchar(10) |
| 产品编号 |
ChanPinMingCheng | nchar(10) |
| 产品名称 |
CaiGouDanJia | decimal(18, 2) |
| 采购单价 |
YiJiXiaoShouJia | decimal(18, 2) |
| 销售价 |
JianJie | nchar(50) |
| 简介 |
GengXinShiJian | datetime |
| 更新时间 |
YouXiaoFou | bit |
| 有效否 |
表5:仓位表(pw_CangWei)
列名 | 数据类型 | 主键/外键 | 说明 |
CangWeiID | int | 主键 | 仓位ID |
MenDianID | int | 外键 | 门店ID |
CangWeiLeiXingID | int | 外键 | 仓位类型ID |
CangWeiMingCheng | nchar(50) |
| 仓位名称 |
PaiXuHao | int |
| 排序号 |
GengXinShiJian | datetime |
| 更新时间 |
YouXiaoFou | bit |
| 有效否 |
BeiZhu | nchar(50) |
| 备注 |
表6:出入库表(pw_ChuRuKuDanJu)
列名 | 数据类型 | 主键/外键 | 说明 |
ChuRuKuDanID | int | 主键 | 出入库单据ID |
LuRuYuanID | int | 外键 | 录入员ID |
DanJuBianHao | nchar(10) |
| 单据编号 |
DanJuRiQi | date |
| 单据日期 |
ShenHeFou | bit |
| 审核否 |
BeiZhu | nchar(50) |
| 备注 |
RuKuFou | bit |
| 入库否 |
YouXiaoFou | bit |
| 有效否 |
表7:出入库明细表(pw_ChuRuKuDanJuMingXi)
列名 | 数据类型 | 主键/外键 | 说明 |
ChuRuKuDanMingXiID | int | 主键 | 出入库明细ID |
ChuRuKuDanJuID | int | 外键 | 出入库单据ID |
ChanPinID | int | 外键 | 产品ID |
CangWeiID | int | 外键 | 仓位ID |
ShuLiang | int | 外键 | 数量 |
JinE | decimal(18, 2) |
| 金额 |
YouXiaoFou | bit |
| 有效否 |
表8:员工表(sys_YuanGong)
列名 | 数据类型 | 主键/外键 | 说明 |
YuanGongID | int | 主键 | 员工ID |
LuRuYuanID | int | 外键 | 录入员ID |
YuanGongZhuangTaiID | int | 外键 | 员工状态ID |
MenDianID | int | 外键 | 门店ID |
ZhiWuID | Int | 外键 | 职务 |
JueSeID | int | 外键 | 角色ID |
YuanGongBianHao | nchar(50) |
| 员工编号 |
YuanGongXingMing | nchar(50) |
| 员工姓名 |
XingBie | nchar(50) |
| 性别 |
JiGuan | nchar(50) |
| 籍贯 |
XueLi | nchar(50) |
| 学历 |
ChuShengRiQi | date |
| 出生日期 |
ShenFenZhengHao | nchar(18) |
| 身份证号 |
RuZhiRiQi | data |
| 入职日期 |
RuZhiDiDian | nchar(50) |
| 入职地点 |
YiDongShouJi | int |
| 移动手机 |
JiaTingDianHua | nchar(50) |
| 家庭电话 |
DiXin | decimal(18, 2) |
| 底薪 |
JiShiFou | bit |
| 技术否 |
HunFou | bit |
| 婚否 |
TongXunDiZhi | nchar(50) |
| 通讯地址 |
BeiZhu | nchar(50) |
| 备注 |
MiMa | nchar(50) |
| 密码 |
表9:品牌表(sys_PinPai)
列名 | 数据类型 | 主键/外键 | 说明 |
PinPaiID | int | 主键 | 品牌ID |
LuRuYuanID | int | 外键 | 录入员 |
PinPaiMingCheng | Nchar(10) |
| 品牌名称 |
PaiXuHao | int |
| 排序号 |
GengXinShiJian | decimal(18, 2) |
| 更新时间 |
YouXiaoFou | Bit |
| 有效否 |
BeiZhu | Nchar(50) |
| 备注 |
表10:客户表(sys_KeHu)
列名 | 数据类型 | 主键/外键 | 说明 |
KeHuID | int | 主键 | 客户ID |
LuRuYuanID | int | 外键 | 录入员ID |
KeHuLeiXingID | int | 外键 | 客户类型ID |
SuoShuMenDianID | int | 外键 | 所属门店ID |
KeHuBianHao | decimal(18, 2) |
| 客户编号 |
KeHuXingMing | Nchar(20) |
| 客户姓名 |
XingBie | Nchar(10) |
| 性别 |
ShenFenZhengHao | Nchar(18) |
| 身份证号 |
YiDongShouJi | Nchar(13) |
| 移动手机 |
JiaTingDianHua | Nchar(10) |
| 家庭电话 |
ChuShenRiQi | Date |
| 出生日期 |
TongXunDiZhi | Date |
| 通讯地址 |
KeHuJiFen | Nchar(10) |
| 客户积分 |
SuoShuDiFang | Nchar(10) |
| 所属地方 |
QQHao | Int |
| QQ号 |
WeiXinHao | Nchar(10) |
| 维修号 |
WeiBoHao | Nchar(10) |
| 微博号 |
GenXinShiJian | datetime |
| 更新时间 |
BeiZhi | Nchar(10) |
| 备注 |
YouXiaoFou | Bit |
| 有效否 |
表11:当日最大单号数表(DangRiZuiDaDanHaoShu)
列名 | 数据类型 | 主键/外键 | 说明 |
ZuiDaDanHaoShuID | int | 主键 | 最大单号数ID |
CaiGouDanHao | int |
| 采购单号 |
CaiGouTuiHuoDanHao | int |
| 采购退货单号 |
XiaoShouDanHao | int |
| 销售单号 |
XiaoShouTuiHuoDanHao | int |
| 销售退货单号 |
XiaoShouBaoXiuDanHao | int |
| 销售保修单号 |
XiaoShouHuanHuoDanHao | int |
| 销售换货单号 |
GongDanBianHao | int |
| 工单编号 |
ChuKuDanHao | int |
| 出库单号 |
RuKuDanHao | int |
| 入库单号 |
JieZhangDanHao | int |
| 结账单号 |
WaiPaiDanJuHao | int |
| 外派单据号 |
QuBiaoFuKuanDanJuHao | int |
| 取表付款单据号 |
FuKuanBianHao | int |
| 付款单号 |
一、录入数据
1、绑定下拉框
第一步:存储过程
IF (@TYPE = 'frmXiaoShouKaiDan_Insert_Load_SelectTrueBaoXiuQi')
BEGIN
SELECT BaoXiuQiID, RTRIM(BaoXiuQiMingCheng) AS BaoXiuQiMingCheng,YouXiaoFou
--查询的列
FROM sys_BaoXiuQi
--从哪个表查询
WHERE YouXiaoFou=1
--查询条件
END
IF (@TYPE = 'frmXiaoShouKaiDan_Insert_Load_SelectXiaoShouLeiXing')
BEGIN
SELECT ShuXingMingXiID AS ShuXingMingXiID,LTRIM(RTRIM(ShuXingMingXiMingCheng))AS ShuXingMingXiMingCheng
--查询的列
FROM sys_ShuXingMingXi
--从哪个表查询
WHERE ShuXingJiHeID=18 AND YouXiaoFou=1
--查询条件
END
IF (@TYPE = 'frmXiaoShouKaiDan_Insert_Load_SelectZhongBiaoKuanShi')
BEGIN
SELECT ShuXingMingXiID AS ShuXingMingXiID,LTRIM(RTRIM(ShuXingMingXiMingCheng))AS ShuXingMingXiMingCheng
--查询的列
FROM sys_ShuXingMingXi
--从哪个表查询
WHERE ShuXingJiHeID=28 AND YouXiaoFou=1
--查询条件
END
第二步:逻辑层(BLL)代码
DALPublic.DALMethod myDALMethod = new DALPublic.DALMethod();//实例化数据层(DAL)
public DataTable frmXiaoShouKaiDan_Insert_Load_SelectTrueBaoXiuQi()//方法名称
{
SqlParameter[] mySqlParameters ={
new SqlParameter("@TYPE",SqlDbType.Char)//声明变量数组
};
mySqlParameters[0].Value = "frmXiaoShouKaiDan_Insert_Load_SelectTrueBaoXiuQi";//给变量数组的第一个赋值
return myDALMethod.QueryDataTable("frmXiaoShouKaiDan_Insert", mySqlParameters);//第一个参数是数据库名称,第二个参数是数组
}
public DataTable frmXiaoShouKaiDan_Insert_Load_SelectXiaoShouLeiXing()
{
SqlParameter[] mySqlParameters ={
new SqlParameter("@TYPE",SqlDbType.Char)
};
mySqlParameters[0].Value = "frmXiaoShouKaiDan_Insert_Load_SelectXiaoShouLeiXing";
return myDALMethod.QueryDataTable("frmXiaoShouKaiDan_Insert", mySqlParameters);
}
public DataTable frmXiaoShouKaiDan_Insert_Load_SelectZhongBiaoKuanShi()
{
SqlParameter[] mySqlParameters ={
new SqlParameter("@TYPE",SqlDbType.Char)
};
mySqlParameters[0].Value = "frmXiaoShouKaiDan_Insert_Load_SelectZhongBiaoKuanShi";
return myDALMethod.QueryDataTable("frmXiaoShouKaiDan_Insert", mySqlParameters);
}
第三步:界面层(UIL)代码,窗体的Load事件
private void frmXiaoShouKaiDan_Insert_Load(object sender, EventArgs e)
{
保修期.DataSource = myfrmXiaoShouKaiDan_Insert.frmXiaoShouKaiDan_Insert_Load_SelectTrueBaoXiuQi();//绑定数据源
保修期.DisplayMember = "BaoXiuQiMingCheng";//绑定显示文本
保修期.ValueMember = "BaoXiuQiID";//绑定ID
cboKuanShi.DataSource = myfrmXiaoShouKaiDan_Insert.frmXiaoShouKaiDan_Insert_Load_SelectZhongBiaoKuanShi();//绑定数据源
cboKuanShi.DisplayMember = "ShuXingMingXiMingCheng";//绑定显示文本
cboKuanShi.ValueMember = "ShuXingMingXiID";//绑定ID
cboXiaoShouLeiXing.DataSource = myfrmXiaoShouKaiDan_Insert.frmXiaoShouKaiDan_Insert_Load_SelectXiaoShouLeiXing();
cboXiaoShouLeiXing.DisplayMember = "ShuXingMingXiMingCheng";
cboXiaoShouLeiXing.ValueMember = "ShuXingMingXiID";
dgvChanPinMingXi.AutoGenerateColumns = false;//设置DGV不能自动创建列
dgvChanPinMingXi.AllowUserToAddRows = false;//设置DGV不能手动添加行
txtXiaoShouDanBianHao.Text = "自动生成";//给销售单编号文本框赋值
}
2、选择客户,界面如(图3)、(图4)所示
第一步:添加客户的单击事件
private void btnTianJiaKeHu_Click(object sender, EventArgs e)
{
if (intGeShu > 0)//如果是按了[再录一条]按钮,则不能更换客户
{
MessageBox.Show("当前客户已绑定,如果不同客户请结账后再录入!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
GongGongChuangTi.frmKeHu myfrmKeHu = new GongGongChuangTi.frmKeHu();//实例化选择客户窗体
myfrmKeHu.ShowDialog();//弹出窗体
if (GongGongChuangTi.frmKeHu.pubKeHuID != 0)//判断选择客户窗体是否按了关闭或者返回,如果是就不更改客户信息
{
intKeHuID = GongGongChuangTi.frmKeHu.pubKeHuID;//赋值语句,GongGongChuangTi.frmKeHu这个前缀是选择客户窗体的静态变量
txtKeHuXingMing.Text = GongGongChuangTi.frmKeHu.pubKeHuXingMing;
txtLianXiDianHua.Text = GongGongChuangTi.frmKeHu.pubLianXiDianHua;
txtLianXiDiZhi.Text = GongGongChuangTi.frmKeHu.pubLianXiDiZhi;
}
}
第二步:选择客户的Load事件
public static int pubKeHuID = 0;
public static string pubKeHuXingMing = "";
public static string pubLianXiDianHua = "";
public static string pubLianXiDiZhi = "";
public static string pubKeHuLeiXing = "";
private void frmKeHu_Load(object sender, EventArgs e)
{
dgvKeHe.AllowUserToAddRows = false;//禁止手动添加行
dgvKeHe.AutoGenerateColumns = false;//禁止自动添加列
dgvKeHe.ReadOnly = true;//禁止编辑DGV
cboYeDaXiao.Text = "20";//每页数量默认为20行
pubKeHuID = 0;//一打开窗体就把客户信息清空,方便判断是否确定选择
pubKeHuXingMing = "";
pubLianXiDianHua = "";
pubLianXiDiZhi = "";
pubKeHuLeiXing = "";
btnChaXun_Click(null, null);//调用查询方法
}
第三步:客户的模糊查询、分页功能
(1)数据库的存储过程
CREATE PROCEDURE ShuJuBiaoGe_FenYeChaXun
@tblName varchar(4000), -- 表名
@strGetFields varchar(4000) = '*', -- 需要返回的列
@fldName varchar(255)='''', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit output, -- 返回记录总数, 非0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@strWhere varchar(1500) = '''' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @temp varchar(100)--截取后的临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''''
set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end
--以上代码的意思是如果@doCount传递过来的不是,就执行总数统计。以下的所有代码都是@doCount为的情况:
else
begin
if @OrderType != 0--升序
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '
from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '
from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @temp=(select SUBSTRING(@fldName,charindex('.',@fldName,1)+1,len(@fldName)) )
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '(tblTmp.'+ @temp + ')
from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + '
from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '(tblTmp.'
+ @temp + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
print @strSQL
exec (@strSQL)
(2)逻辑层(BLL)代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace BLL
{
public class BLLGongGongFenYe
{
DALPublic.DALMethod myDALMethod = new DALPublic.DALMethod();
public DataTable frmXiaoShouDan_select_TongYongFenYe(string strTblName, string strGetFields,
string strFldName, int intPageSize, int intPageIndex,
bool blDoCount, bool blOrderType, string strWhere)
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@tblName",SqlDbType.VarChar,4000 ),
new SqlParameter("@strGetFields",SqlDbType.VarChar,4000 ),
new SqlParameter("@fldName",SqlDbType.VarChar,255 ),
new SqlParameter("@PageSize",SqlDbType.Int ),
new SqlParameter("@PageIndex",SqlDbType.Int ),
new SqlParameter("@doCount",SqlDbType.Bit ),
new SqlParameter("@OrderType",SqlDbType.Bit ),
new SqlParameter("@strWhere",SqlDbType.VarChar,1500 ),
};
mySqlParameters[0].Value = strTblName;
mySqlParameters[1].Value = strGetFields;
mySqlParameters[2].Value = strFldName;
mySqlParameters[3].Value = intPageSize;
mySqlParameters[4].Value = intPageIndex;
mySqlParameters[5].Value = blDoCount;
mySqlParameters[6].Value = blOrderType;
mySqlParameters[7].Value = strWhere;
DataTable dt = myDALMethod.QueryDataTable("ShuJuBiaoGe_FenYeChaXun", mySqlParameters);
return dt;
}
}
}
(3)界面层(UIL)代码
#region 查询所需有的字段
decimal decYueShu; //页数
#endregion
#region 数据库查询需要的参数,表名,字段名,排序字段,每页的大小,查询第几页,是否查询总记录数(true只查询总记录数,false查询表数据),排序类型(true倒序,false 升序),where条件
string strTblName = " sys_KeHu INNER JOIN sys_KeHuLeiXing ON sys_KeHu.KeHuLeiXingID = sys_KeHuLeiXing.KeHuLeiXingID";
string strGetFields = " sys_KeHu.KeHuID, RTRIM(sys_KeHu.KeHuBianHao) AS KeHuBianHao, RTRIM(sys_KeHu.KeHuXingMing) AS KeHuXingMing, RTRIM(sys_KeHu.XingBie) AS XingBie,"+
" RTRIM(sys_KeHu.YiDongShouJi) AS YiDongShouJi, RTRIM(sys_KeHu.JiaTingDianHua) AS JiaTingDianHua, RTRIM(sys_KeHu.DianZiYouXiang) AS DianZiYouXiang,"+
" RTRIM(sys_KeHu.ShenFenZhengHao) AS ShenFenZhengHao, sys_KeHu.ChuShenRiQi, RTRIM(sys_KeHu.TongXunDiZhi) AS TongXunDiZhi,"+
" RTRIM(sys_KeHu.BeiZhi) AS BeiZhi,sys_KeHu.GenXinShiJian, RTRIM(sys_KeHuLeiXing.KeHuLeiXing) as KeHuLeiXing, sys_KeHu.KeHuLeiXingID";
string strFldName = "sys_KeHu.KeHuID";//排序的字段名
int intPageSize = 20;//页尺寸
int intPageIndex = 1;//页码
string strWhere; //查询条件 (注意: 不要加 where)
#endregion
BLL.BLLGongGongFenYe myBLLGongGongFenYe = new BLL.BLLGongGongFenYe();
#region 查询
/// <param name="intXianShiHang">每页显示的行数</param>
/// <param name="intDangQianYeShu">当前页数</param>
/// <param name="bolChaXingZhongShu"></param>
/// <param name="bolDaoXu">如果是true就是倒序</param>
/// <returns></returns>
DataTable ChaXunShuJu(int intXianShiHang, int intDangQianYueShu, bool bolChaXingZhongShu, bool bolDaoXu)
{
strWhere = "sys_KeHu.YouXiaoFou=1";//初始化查询条件
if (txtChaXunNeiRong.Text != "")//判断文本框有没有输入内容
{
strWhere += " and (sys_KeHu.KeHuXingMing like '%'+'" + txtChaXunNeiRong.Text.Trim() + "'+'%' or sys_KeHu.KeHuBianHao like '%'+'" + txtChaXunNeiRong.Text.Trim() + "'+'%'" +
" or sys_KeHu.XingBie like '%'+'" + txtChaXunNeiRong.Text.Trim() + "'+'%')";
//累加模糊查询内容
}
decYueShu = Convert.ToDecimal(myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intPageSize, intPageIndex, true, true, strWhere).Rows[0][0]);//获取查询结果的行数
if (decYueShu / intXianShiHang <= Convert.ToInt32(decYueShu / intXianShiHang))//判断有多少页
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang);
}
else
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang) + 1;
}
if (intDangQianYueShu > decYueShu)//如果当前页数大于最大页数,则重新复制为最大页数
{
intPageIndex = (int)decYueShu;
intDangQianYueShu = (int)decYueShu;
}
if (intPageIndex == 0 || intDangQianYueShu == 0)//如果当前页数为0,则重新赋值为1
{
intPageIndex = 1;
intDangQianYueShu = 1;
}
lblZhongShu.Text = "/" + decYueShu.ToString();
txtDangQianYeShu.Text = intDangQianYueShu.ToString();
return myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intXianShiHang, intDangQianYueShu, bolChaXingZhongShu, bolDaoXu, strWhere);//返回查询出来的结果
}
#endregion
(4)查询按钮的单击事件
private void btnChaXun_Click(object sender, EventArgs e)
{
intPageIndex = 1;//默认为第一页
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);//查询数据,且赋值给DGV
}
(5)首页按钮的单击事件
private void btnShouYe_Click(object sender, EventArgs e)
{
intPageIndex = 1;//跳到第一页
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
(6)上一页按钮的单击事件
private void btnShangYiYe_Click(object sender, EventArgs e)
{
if (intPageIndex <= 1)//如果已经是第一页,则返回(跳出)
{
return;
}
intPageIndex--;//页数减一
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
(7)下一页按钮的单击事件
private void btnXiaYiYe_Click(object sender, EventArgs e)
{
if (intPageIndex >= decYueShu)//如果是最后一页,则返回(跳出)
{
return;
}
intPageIndex++;//页数加一
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
(8)首页按钮的单击事件
private void btnWeiYe_Click(object sender, EventArgs e)
{
intPageIndex = Convert.ToInt32(decYueShu);//把最大页数赋值给当前页数
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
(9)跳转按钮的单击事件
private void btnTiaoZhuan_Click(object sender, EventArgs e)
{
if (txtTiaoZhuanYeShu.Text == "")//如果跳转页数文本框为空,则返回
{ return; }
if (Convert.ToInt32(txtTiaoZhuanYeShu.Text) > Convert.ToInt32(decYueShu) || Convert.ToInt32(txtTiaoZhuanYeShu.Text) < 1)//判断页数是否在范围内
{
MessageBox.Show("输入的页数不在范围之内", "提示"); return;
}
intPageIndex = Convert.ToInt32(txtTiaoZhuanYeShu.Text);//把跳转页数文本框的值转化成整形,并赋值给当前页数
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
(10)页大小下拉框的下拉框关闭事件
private void cboYeDaXiao_DropDownClosed(object sender, EventArgs e)
{
try
{
intPageSize = Convert.ToInt32(cboYeDaXiao.Text);//设置每页显示的行数
dgvKeHe.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
catch { }
}
第四步:完成选择
(1)DGV的双击事件
private void dgvKeHe_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
try
{
pubKeHuID = Convert.ToInt32(dgvKeHe.CurrentRow.Cells["客户ID"].Value.ToString());
pubKeHuXingMing = dgvKeHe.CurrentRow.Cells["客户姓名"].Value.ToString().Trim();
pubLianXiDianHua = dgvKeHe.CurrentRow.Cells["移动手机"].Value.ToString().Trim();
pubLianXiDiZhi = dgvKeHe.CurrentRow.Cells["家庭地址"].Value.ToString().Trim();
pubKeHuLeiXing = dgvKeHe.CurrentRow.Cells["客户类型"].Value.ToString().Trim();
this.Dispose();//释放窗体的资源
this.Close();//关闭窗体
}
catch { }
}
(2)确定按钮的单击事件
private void btnQueDing_Click(object sender, EventArgs e)
{
if (dgvKeHe.Rows.Count > 0)
{
pubKeHuID = Convert.ToInt32(dgvKeHe.CurrentRow.Cells["客户ID"].Value.ToString());
pubKeHuXingMing = dgvKeHe.CurrentRow.Cells["客户姓名"].Value.ToString().Trim();
pubLianXiDianHua = dgvKeHe.CurrentRow.Cells["移动手机"].Value.ToString().Trim();
pubLianXiDiZhi = dgvKeHe.CurrentRow.Cells["家庭地址"].Value.ToString().Trim();
pubKeHuLeiXing = dgvKeHe.CurrentRow.Cells["客户类型"].Value.ToString().Trim();
this.Dispose();//释放窗体的资源
this.Close();//关闭窗体
}
else
{
MessageBox.Show("当前表格没有数据,请查询!");
}
}
3、选择品牌如(图5)、(图6)所示
第一步:查询品牌
(1)数据库存储过程,参考选择客户
(2)逻辑层代码,参考选择客户
(3)界面层代码
public static int pubPinPaiID = 0;
public static string pubPinPaiMingCheng = "";
#region 查询所需有的字段
decimal decYueShu; //页数
#endregion
#region 数据库查询需要的参数,表名,字段名,排序字段,每页的大小,查询第几页,where条件
string strTblName = "sys_PinPai";
string strGetFields = "rtrim(PinPaiMingCheng) as PinPaiMingCheng, PinPaiID, GengXinShiJian, rtrim(PaiXuHao) as PaiXuHao";
string strFldName = "sys_PinPai.PinPaiID";//排序的字段名
int intPageSize = 20;//页尺寸
int intPageIndex = 1;//页码
string strWhere; //查询条件 (注意: 不要加 where)
#endregion
BLL.BLLGongGongFenYe myBLLGongGongFenYe = new BLL.BLLGongGongFenYe();
#region 查询
DataTable ChaXunShuJu(int intXianShiHang, int intDangQianYueShu, bool bolChaXingZhongShu, bool bolDaoXu)
{
strWhere = "";
strWhere += "sys_PinPai.YouXiaoFou=1";
if (txtChaXunNeiRong.Text != "")
{
strWhere += " and (PinPaiMingCheng like '%'+'" + txtChaXunNeiRong.Text.Trim() + "'+'%' or PaiXuHao like '%'+'" + txtChaXunNeiRong.Text.Trim() + "'+'%')";
}
decYueShu = Convert.ToDecimal(myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intPageSize, intPageIndex, true, true, strWhere).Rows[0][0]);
if (decYueShu / intXianShiHang <= Convert.ToInt32(decYueShu / intXianShiHang))
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang);
}
else
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang) + 1;
}
if (intDangQianYueShu > decYueShu)
{
intPageIndex = (int)decYueShu;
intDangQianYueShu = (int)decYueShu;
}
if (intPageIndex == 0 || intDangQianYueShu == 0)
{
intPageIndex = 1;
intDangQianYueShu = 1;
}
lblZhongShu.Text = "/" + decYueShu.ToString();
txtDangQianYeShu.Text = intDangQianYueShu.ToString();
return myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intXianShiHang, intDangQianYueShu, bolChaXingZhongShu, bolDaoXu, strWhere);
}
#endregion
窗体的Load事件
private void frmPinPai_Load(object sender, EventArgs e)
{
pubPinPaiID = 0;
cboYeDaXiao.Text = "20";
dgvPinPai.AllowUserToAddRows = false;
dgvPinPai.AutoGenerateColumns = false;
btnChaXun_Click(null, null);
}
查询按钮的单击事件
private void btnChaXun_Click(object sender, EventArgs e)
{
intPageIndex = 1;
dgvPinPai.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
}
第二步:完成选择
DGV的双击事件
private void dgvPinPai_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
try
{
pubPinPaiID = Convert.ToInt32(dgvPinPai.CurrentRow.Cells["品牌ID"].Value);
pubPinPaiMingCheng = dgvPinPai.CurrentRow.Cells["品牌名称"].Value.ToString().Trim();
this.Dispose();
this.Close();
}
catch
{ }
}
确定按钮的单击事件
private void btnQueDing_Click(object sender, EventArgs e)
{
if (dgvPinPai.Rows.Count > 0)
{
pubPinPaiID = Convert.ToInt32(dgvPinPai.CurrentRow.Cells["品牌ID"].Value);
pubPinPaiMingCheng = dgvPinPai.CurrentRow.Cells["品牌名称"].Value.ToString().Trim();
this.Dispose();
this.Close();
}
else
{
MessageBox.Show("当前没有可操作的数据,请查询!");
}
}
4、选择产品,界面如(图7)、(图8)所示
(图7)
第一步:录入产品按钮的单击事件,和自定义统计总金额方法
private void btnLuRuChanPin_Click(object sender, EventArgs e)
{
GongGongChuangTi.frmChanPin.dtChanPin.Rows.Clear();//清空选择产品窗体中dtChanPin的行
if (dgvChanPinMingXi.Rows.Count > 0 && GongGongChuangTi.frmChanPin.dtChanPin.Columns.Count > 0)
{
for (int i = 0; i < dgvChanPinMingXi.Rows.Count; i++)
{
GongGongChuangTi.frmChanPin.dtChanPin.Rows.Add(1);//把DGV中已经存在的产品添加到,选择产品窗体的dtChanPin中
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["产品ID"] = dgvChanPinMingXi.Rows[i].Cells["产品ID"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["名称"] = dgvChanPinMingXi.Rows[i].Cells["名称"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["品牌"] = dgvChanPinMingXi.Rows[i].Cells["品牌"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["编号"] = dgvChanPinMingXi.Rows[i].Cells["编号"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["品种"] = dgvChanPinMingXi.Rows[i].Cells["品种"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["销售价格"] = dgvChanPinMingXi.Rows[i].Cells["销售价格"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["销售数量"] = dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["小计"] = dgvChanPinMingXi.Rows[i].Cells["小计"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["所在仓位ID"] = dgvChanPinMingXi.Rows[i].Cells["所在仓位ID"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["所在仓位"] = dgvChanPinMingXi.Rows[i].Cells["所在仓位"].Value.ToString().Trim();
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["库存数"] = dgvChanPinMingXi.Rows[i].Cells["库存数"].Value.ToString().Trim();
try
{
GongGongChuangTi.frmChanPin.dtChanPin.Rows[i]["保修期"] = dgvChanPinMingXi.Rows[i].Cells["保修期"].Value.ToString().Trim();
}
catch { }
}
}
GongGongChuangTi.frmChanPin myfrmChanPin = new GongGongChuangTi.frmChanPin();//实例化选择产品窗体
myfrmChanPin.ShowDialog();//弹出窗体。
if (GongGongChuangTi.frmChanPin.pubQueDingFou)//判断是否确定选择
{
dgvChanPinMingXi.Rows.Clear();//清空DGV
DataTable dtChanPin=GongGongChuangTi.frmChanPin.dtChanPin;
for (int i = 0; i < GongGongChuangTi.frmChanPin.dtChanPin.Rows.Count; i++)//循环把选择到的产品,添加到DGV
{
dgvChanPinMingXi.Rows.Add(1);
dgvChanPinMingXi.Rows[i].Cells["产品ID"].Value = dtChanPin.Rows[i]["产品ID"];
dgvChanPinMingXi.Rows[i].Cells["名称"].Value = dtChanPin.Rows[i]["名称"];
dgvChanPinMingXi.Rows[i].Cells["品牌"].Value = dtChanPin.Rows[i]["品牌"];
dgvChanPinMingXi.Rows[i].Cells["编号"].Value = dtChanPin.Rows[i]["编号"];
dgvChanPinMingXi.Rows[i].Cells["品种"].Value = dtChanPin.Rows[i]["品种"];
dgvChanPinMingXi.Rows[i].Cells["销售价格"].Value = dtChanPin.Rows[i]["销售价格"];
dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value = dtChanPin.Rows[i]["销售数量"];
dgvChanPinMingXi.Rows[i].Cells["小计"].Value = dtChanPin.Rows[i]["小计"];
dgvChanPinMingXi.Rows[i].Cells["所在仓位ID"].Value = dtChanPin.Rows[i]["所在仓位ID"];
dgvChanPinMingXi.Rows[i].Cells["所在仓位"].Value = dtChanPin.Rows[i]["所在仓位"];
dgvChanPinMingXi.Rows[i].Cells["库存数"].Value = dtChanPin.Rows[i]["库存数"];
try
{
dgvChanPinMingXi.Rows[i].Cells["保修期"].Value = Convert.ToInt32(dtChanPin.Rows[i]["保修期"]);
}
catch { }
}
JiSuanZongJinE();
}
}
private void JiSuanZongJinE()//自定义的计算总价,和判断输入的方法
{
decimal decZongJinE = 0;//声明一个十进制数型变量
for (int i = 0; i < dgvChanPinMingXi.Rows.Count; i++)
{
decimal decJiaGe = 0;
try
{
decJiaGe = Convert.ToDecimal(dgvChanPinMingXi.Rows[i].Cells["销售价格"].Value);//如果转换形态的时候出错,就是输入错误
if (decJiaGe < 0)
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售价格】不能小于零!");
dgvChanPinMingXi.Rows[i].Cells["销售价格"].Value = 0;
decJiaGe = 0;
}
}
catch
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售价格】填写不正确!");
dgvChanPinMingXi.Rows[i].Cells["销售价格"].Value = 0;
decJiaGe = 0;
}
decimal decShuLiang = 0;
try
{
decShuLiang = Convert.ToDecimal(dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value);//如果转换形态的时候出错,就是输入错误
if (decShuLiang < 1)
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售数量】不能小于一!");
dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value = 1;
decShuLiang = 1;
}
if (decShuLiang > Convert.ToDecimal(dgvChanPinMingXi.Rows[i].Cells["库存数"].Value))
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售数量】不能大于库存数!");
dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value = 1;
decShuLiang = 1;
}
if (decShuLiang % 1 != 0)
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售数量】不能为小数!");
dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value = 1;
decShuLiang = 1;
}
}
catch
{
MessageBox.Show("第【" + (i + 1).ToString() + "】行,的【销售数量】填写不正确!");
dgvChanPinMingXi.Rows[i].Cells["销售数量"].Value = 1;
decShuLiang = 1;
}
dgvChanPinMingXi.Rows[i].Cells["小计"].Value = decJiaGe * decShuLiang;
decZongJinE += Convert.ToDecimal(dgvChanPinMingXi.Rows[i].Cells["小计"].Value);//累加每行的小计,也就是总金额
}
}
第二步:查询产品
(1)数据库存储过程,参考选择客户
(2)逻辑层代码,参考选择客户
(3)界面层代码#region 查询产品需要的字段
decimal decYueShu;
int zongYeShu = 0;
int intZongJiLuShu = 0; //总记录数
private TabControl myTabControl = null;//选项卡
int intPinZhong = 0; //产品品种
System.Data.DataTable cbodataSource; //绑定下拉框需要的数据源
#endregion
#region 数据库查询需要的参数,表名,字段名,排序字段,每页的大小,查询第几页,是否查询总记录数(true只查询总记录数,false查询表数据),排序类型(true倒序,false 升序),where条件
//数据库查询时from关键字后面的语句
string strTblName = " sys_ChanPinZiLiao INNER JOIN sys_PinPai ON sys_ChanPinZiLiao.ChanPinPinPaiID = sys_PinPai.PinPaiID INNER JOIN sys_ShuXingMingXi ON sys_ChanPinZiLiao.ChanPinLeiXingID = sys_ShuXingMingXi.ShuXingMingXiID INNER JOIN sys_ShuXingMingXi AS sys_ShuXingMingXi_1 ON sys_ChanPinZiLiao.DanWeiID = sys_ShuXingMingXi_1.ShuXingMingXiID INNER JOIN sys_PinZhong ON sys_ChanPinZiLiao.ChanPinZhongLeiID = sys_PinZhong.PinZhongID INNER JOIN sys_KuCun ON sys_ChanPinZiLiao.ChanPinID = sys_KuCun.ChanPinID INNER JOIN sys_CangWei ON sys_KuCun.CangWeiID = sys_CangWei.CangWeiID";
string strGetFields = "sys_ChanPinZiLiao.ChanPinID, RTRIM(sys_ChanPinZiLiao.ChanPinMingCheng) AS ChanPinMingCheng, RTRIM(sys_ChanPinZiLiao.ChanPinBianHao) AS ChanPinBianHao,"+
"sys_ChanPinZiLiao.ChanPinLeiXingID, sys_ChanPinZiLiao.ChanPinZhongLeiID, sys_ChanPinZiLiao.ChanPinPinPaiID, RTRIM(sys_PinPai.PinPaiMingCheng) AS PinPaiMingCheng,"+
"RTRIM(sys_ChanPinZiLiao.CaiGouDanJia) AS CaiGouDanJia, RTRIM(sys_ChanPinZiLiao.YiJiXiaoShouJia) AS YiJiXiaoShouJia, RTRIM(sys_ChanPinZiLiao.GengXinShiJian) AS GengXinShiJian,"+
"sys_ChanPinZiLiao.DanWeiID, RTRIM(sys_ShuXingMingXi.ShuXingMingXiMingCheng) AS ChanPinLeiXing, RTRIM(sys_ShuXingMingXi_1.ShuXingMingXiMingCheng) AS DanWei,"+
"RTRIM(sys_PinZhong.PinZhongMingCheng) AS PinZhongMingCheng, sys_ChanPinZiLiao.ZuiDaKuCun, sys_ChanPinZiLiao.ZuiShaoKuCun, sys_ChanPinZiLiao.ChanPinBianHao AS Expr1,"+
"sys_ChanPinZiLiao.ChanPinMingCheng AS Expr2, sys_ChanPinZiLiao.CaiGouDanJia AS Expr3, sys_ChanPinZiLiao.YiJiXiaoShouJia AS Expr4, sys_ChanPinZiLiao.BaoXiuQiXian,"+
"sys_ChanPinZiLiao.JianJie, sys_ChanPinZiLiao.GengXinShiJian AS Expr5, sys_CangWei.CangWeiID, sys_KuCun.KuCunShuLiang, sys_CangWei.CangWeiMingCheng";
string strFldName = " sys_ChanPinZiLiao.ChanPinID ";
int intPageSize = 20;
int intPageIndex = 1;
string strWhere;
#endregion
BLL.BLLGongGongFenYe myBLLGongGongFenYe = new BLL.BLLGongGongFenYe();
#region 查询
DataTable ChaXunShuJu(int intXianShiHang, int intDangQianYueShu, bool bolChaXingZhongShu, bool bolDaoXu)
{
strWhere = "";
strWhere += "sys_ChanPinZiLiao.YouXiaoFou=1";
if (Convert.ToInt32(cboChanPinLeiXing.SelectedValue)!= 0)
{
strWhere += "and (sys_ChanPinZiLiao.ChanPinLeiXingID = '"+cboChanPinLeiXing.SelectedValue+"')";
}
if (Convert.ToInt32(cboCangWei.SelectedValue) != 0)
{
strWhere += "and (sys_CangWei.CangWeiID = '" + cboCangWei.SelectedValue + "')";
}
if (txtChanPinMingCheng.Text != "")
{
strWhere += " and (sys_ChanPinZiLiao.ChanPinMingCheng like '%'+'" + txtChanPinMingCheng.Text.Trim() + "'+'%' or sys_ChanPinZiLiao.ChanPinBianHao like '%'+'" + txtChanPinMingCheng.Text.Trim() + "'+'%'" +
" or sys_PinPai.PinPaiMingCheng like '%'+'" + txtChanPinMingCheng.Text.Trim() + "'+'%'or sys_PinZhong.PinZhongMingCheng like '%'+'"+txtChanPinMingCheng.Text.Trim()+"'+'%')";
}
decYueShu = Convert.ToDecimal(myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intPageSize, intPageIndex, true, true, strWhere).Rows[0][0]);
if (decYueShu / intXianShiHang <= Convert.ToInt32(decYueShu / intXianShiHang))
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang);
}
else
{
decYueShu = Convert.ToInt32(decYueShu / intXianShiHang) + 1;
}
if (intDangQianYueShu > decYueShu)
{
intPageIndex = (int)decYueShu;
intDangQianYueShu = (int)decYueShu;
}
if (intPageIndex == 0 || intDangQianYueShu == 0)
{
intPageIndex = 1;
intDangQianYueShu = 1;
}
lblZhongShu.Text = "/" + decYueShu.ToString();
txtDangQianYeShu.Text = intDangQianYueShu.ToString();
return myBLLGongGongFenYe.frmXiaoShouDan_select_TongYongFenYe(strTblName, strGetFields, strFldName, intXianShiHang, intDangQianYueShu, bolChaXingZhongShu, bolDaoXu, strWhere);
}
#endregion
BLL.BLL_CangKuGuanLi.frmChanPinZiLiao myfrmChanPinZiLiao = new BLL.BLL_CangKuGuanLi.frmChanPinZiLiao();
public static DataTable dtChanPin=new DataTable();
public static bool pubQueDingFou = false;
public static int pubPinPaiID = 0;
public static string pubPinPaiMingCheng = "";
(4)窗体的Load事件
private void frmChanPin_Load(object sender, EventArgs e)
{
dgvChanPinZiLiao.AllowUserToAddRows = false;
dgvChanPinZiLiao.AutoGenerateColumns = false;
dgvChanPinZiLiao.ReadOnly = true;
DataTable dataSource = myfrmChanPinZiLiao.frmChanPinZiLiao_Insert_Load_SelectQuanBuChanPinLeiXing();
dataSource.Rows.Add("0", "全部");//添加行,0是"ChanPinLeiXingID",全部是"ChanPinLeiXingMingCheng"
cboChanPinLeiXing.DataSource = dataSource;
cboChanPinLeiXing.DisplayMember = "ChanPinLeiXingMingCheng";
cboChanPinLeiXing.ValueMember = "ChanPinLeiXingID";
cboChanPinLeiXing.SelectedValue = 0;
DataTable dataSource1 = myfrmChanPinZiLiao.frmChanPinZiLiao_Insert_Load_SelectQuanBuCangWei();
dataSource1.Rows.Add("全部", "0");
cboCangWei.DataSource = dataSource1;
cboCangWei.DisplayMember = "CangWeiMingCheng";
cboCangWei.ValueMember = "CangWeiID";
cboCangWei.SelectedIndex = 0;
cboYeDaXiao.Text = "20";
pubQueDingFou = false;//确定否,先复制为False,按了确定按钮,再改为True
if (dtChanPin.Columns.Count < 1)
{
dtChanPin.Columns.Add("产品ID", typeof(string));
dtChanPin.Columns.Add("名称", typeof(string));
dtChanPin.Columns.Add("品牌", typeof(string));
dtChanPin.Columns.Add("编号", typeof(string));
dtChanPin.Columns.Add("品种", typeof(string));
dtChanPin.Columns.Add("销售价格", typeof(string));
dtChanPin.Columns.Add("销售数量", typeof(string));
dtChanPin.Columns.Add("小计", typeof(string));
dtChanPin.Columns.Add("所在仓位ID", typeof(string));
dtChanPin.Columns.Add("所在仓位", typeof(string));
dtChanPin.Columns.Add("库存数", typeof(string));
dtChanPin.Columns.Add("保修期", typeof(string));
dtChanPin.Columns.Add("保修备注", typeof(string));
}
btnChaXun_Click(null, null); //调用查询按钮的单击事件
}
(5)查询按钮的单击事件
private void btnChaXun_Click(object sender, EventArgs e)
{
intPageIndex = 1;
dgvChanPinZiLiao.DataSource = ChaXunShuJu(intPageSize, intPageIndex, false, true);
for (int i = 0; i < dtChanPin.Rows.Count; i++)
{
for (int j = 0; j < dgvChanPinZiLiao.Rows.Count; j++)//双重循环判断,已经在DGV存在的就默认打钩
{
if (Convert.ToInt32(dgvChanPinZiLiao.Rows[j].Cells["产品ID"].Value) == Convert.ToInt32(dtChanPin.Rows[i]["产品ID"]) &&
Convert.ToInt32(dgvChanPinZiLiao.Rows[j].Cells["所在仓位ID"].Value) == Convert.ToInt32(dtChanPin.Rows[i]["所在仓位ID"]))
{
dgvChanPinZiLiao.Rows[j].Cells["选择"].Value = true;
break;
}
}
}
for (int i = 0; i < dtXiaoShouDanMingXi.Rows.Count; i++)
{
for (int j = 0; j < dgvChanPinZiLiao.Rows.Count; j++)//双重循环判断,如果是修改销售单,打开的选择产品窗体,则把之前已经选择的产品库存数回滚
{
if (Convert.ToInt32(dtXiaoShouDanMingXi.Rows[i]["ChanPinID"]) == Convert.ToInt32(dgvChanPinZiLiao.Rows[j].Cells["产品ID"].Value) &&
Convert.ToInt32(dtXiaoShouDanMingXi.Rows[i]["CangWeiID"]) == Convert.ToInt32(dgvChanPinZiLiao.Rows[j].Cells["所在仓位ID"].Value))
{
dgvChanPinZiLiao.Rows[j].Cells["库存数"].Value = Convert.ToDecimal(dgvChanPinZiLiao.Rows[j].Cells["库存数"].Value) + Convert.ToDecimal(dtXiaoShouDanMingXi.Rows[i]["ShuLiang"]);
break;
}
}
}
}
(6)DGV的,在单元格上释放鼠标按钮时发生事件
private void dgvChanPinZiLiao_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
{
try
{
for (int i = 0; i < dgvChanPinZiLiao.SelectedRows.Count; i++)
{
if (Convert.ToDecimal(dgvChanPinZiLiao.SelectedRows[i].Cells["库存数"].Value) <= 0)//判断有没有库存数
{
string strChanPinMingCheng = dgvChanPinZiLiao.SelectedRows[i].Cells["名称"].Value.ToString().Trim();
string strCangKuMingCheng = dgvChanPinZiLiao.SelectedRows[i].Cells["所在仓位"].Value.ToString().Trim();
MessageBox.Show("产品【"+strChanPinMingCheng+"】在仓位【"+strCangKuMingCheng+"】没有库存,不能进行选择!","提示!",MessageBoxButtons.OK,MessageBoxIcon.Asterisk);
return;
}
if (Convert.ToBoolean(dgvChanPinZiLiao.SelectedRows[i].Cells["选择"].FormattedValue) == true)//判断是否已经选择
{
dgvChanPinZiLiao.SelectedRows[i].Cells["选择"].Value = false;//变为未选
for (int intSuoYin = 0; intSuoYin < dtChanPin.Rows.Count; intSuoYin++)
{
if (Convert.ToInt32(dgvChanPinZiLiao.SelectedRows[i].Cells["产品ID"].Value) == Convert.ToInt32(dtChanPin.Rows[intSuoYin]["产品ID"])&&
Convert.ToInt32(dgvChanPinZiLiao.SelectedRows[i].Cells["所在仓位ID"].Value) == Convert.ToInt32(dtChanPin.Rows[intSuoYin]["所在仓位ID"]))
{
dtChanPin.Rows.RemoveAt(intSuoYin);//移除选择的行
break;
}
}
}
else//如果还没选择,则加上选的这一行
{
dgvChanPinZiLiao.SelectedRows[i].Cells["选择"].Value = true;//变为已选
dtChanPin.Rows.Add(1);
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["产品ID"] = dgvChanPinZiLiao.SelectedRows[i].Cells["产品ID"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["名称"] = dgvChanPinZiLiao.SelectedRows[i].Cells["名称"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["品牌"] = dgvChanPinZiLiao.SelectedRows[i].Cells["品牌"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["编号"] = dgvChanPinZiLiao.SelectedRows[i].Cells["编号"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["品种"] = dgvChanPinZiLiao.SelectedRows[i].Cells["品种"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["销售价格"] = "0";
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["销售数量"] = 1;
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["小计"] = "0";
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["所在仓位ID"] = dgvChanPinZiLiao.SelectedRows[i].Cells["所在仓位ID"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["所在仓位"] = dgvChanPinZiLiao.SelectedRows[i].Cells["所在仓位"].Value.ToString().Trim();
dtChanPin.Rows[dtChanPin.Rows.Count - 1]["库存数"] = dgvChanPinZiLiao.SelectedRows[i].Cells["库存数"].Value.ToString().Trim();
}
}
DataTable dt = dtChanPin;
}
catch { }
}
(7)确定按钮
private void btnQueDing_Click(object sender, EventArgs e)
{
pubQueDingFou = true;
this.Dispose();
this.Close();
}
注:此文章只供学习参考,禁止用于商业用途。