/// <summary>
/// 高级查询，返回“客户名称”
/// </summary>
/// <returns></returns>
public static string Find_CustName()
{
string strCustName = "";
WFilter wf = new WFilter(0, "CustName", true);
wf.tableName = "T_CustomerInf"; //表名
wf.strSql = "select CustID as 客户编号, CustName as 客户名称, CustType as 类别,PinYinCode as 拼音助记码,whichTrade as 所在行业, City as 城市地区,Province as 省," +
"communicateAddr as 通信地址,BankAccount as 银行帐号, CredDegree as 信用等级, InvoiceTitle as 发票抬头 " +
"from T_CustomerInf";
wf.s_items.Add("客户编号,CustID,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("拼音助记码,PinYinCode,C");
wf.s_items.Add("类别,CustType.CName,C");
wf.s_items.Add("所在行业,whichTrade,C");
wf.s_items.Add("通信地址,communicateAddr,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
return(strCustName);
}
strCustName = wf.Return_Items[0].Trim();//客户名称
}
return(strCustName);
}
/// <summary>
/// 高级查询，返回“物料名称”
/// </summary>
/// <returns></returns>
public static string Find_MatName()
{
string strMatName = "";
WFilter wf = new WFilter(0, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格,Units 计量单位,Brand 品牌,ProductType 产品种类," +
"ColorType 色彩,Speed 速度等级,Format 幅面,Models 机种,ynStopProduct 是否已停产,ConfigType 配置类型 " +
" from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("品牌,Brand,C");
wf.s_items.Add("产品种类,ProductType,C");
wf.s_items.Add("机种,Models,C");
wf.s_items.Add("配置类型,ConfigType,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
return(strMatName);
}
strMatName = wf.Return_Items[0].Trim();//获得物料名称
}
return(strMatName);
}
/// <summary>
/// 高级查询，返回“用户名”
/// </summary>
/// <returns></returns>
public static string Find_UserName()
{
string strUserName = "";
WFilter wf = new WFilter(0, "UserName", true);
wf.tableName = "T_users"; //表名
wf.strSql = "select T_Users.UserId as 用户编码, T_Users.UserName as 用户名, T_Users.ynAdmin as 是否系统管理员,T_Branch.BName as 所属部门," +
"T_Users.JobPosition as 职位,T_UserType.UTypeName as 类别,T_Users.SmsTel as 接收短信电话号码 " +
"from [T_Users] left join T_Branch " +
"on T_Users.BranchId=T_Branch.BId left join T_UserType on T_Users.DefaultUserType=T_UserType.TypeId";
wf.s_items.Add("用户编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("所属部门,BName,C");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("类别,UTypeName,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
return(strUserName);
}
strUserName = wf.Return_Items[0].Trim();//用户名
}
return(strUserName);
}
private void button1_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "MatID", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格 ,Units 计量单位," +
"Brand 品牌,ProductType 产品种类,ColorType 色彩,Speed 速度等级,Format 幅面, ynStopProduct 是否已停产," +
"ConfigType 配置类型 " + "from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("拼音编码,PinYinCode,C");
wf.s_items.Add("品牌,Brand,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
MessageBox.Show("无对应结果!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.whereTJ = " where MatID='" + wf.Return_Items[0].Trim()+"'";
initDataGridView();
}
}
private void btnFindMat_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select distinct T_MatInf.MatID 物料编号,MatName 物料名称," +
"Specifications 型号规格 ,Units 计量单位,Brand 品牌,ProductType 产品种类," +
"ColorType 色彩,Speed 速度等级,Format 幅面," +
"ynStopProduct 是否已停产,ConfigType 配置类型,purchaseprice 标准进货价 " +
"from T_MatInf,T_Mat_Rela " +
" where T_Mat_Rela.ParentMatID=T_MatInf.MatID ";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("品牌,Brand,C");
wf.s_items.Add("产品种类,ProductType,C");
wf.s_items.Add("标准进货价,purchaseprice,N");
wf.s_items.Add("配置类型,ConfigType,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
return;
}
this.comboBoxMatName.Text = wf.Return_Items[0].Trim();
}
}
private void button1_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "", true);
wf.strSql = this.BaseSql;
wf.btnOK.Visible = false;
wf.s_items.Add("状态,T_bargains.Bargstatus,C");
wf.s_items.Add("合同类型,T_bargains.ContractType,C");
wf.s_items.Add("保修类别,tb_maintetype.maintetypename,C");
wf.s_items.Add("合同编号,T_bargains.BargId,C");
wf.s_items.Add("单位名称,T_CustomerInf.CustName,C");
wf.s_items.Add("机型,T_bargains.Mtype,C");
wf.s_items.Add("机号,T_bargains.Manufactcode,C");
wf.s_items.Add("机器地址,T_CustomerMac.Mdepart,C");
wf.s_items.Add("收费类型,T_bargains.FeeType,C");
wf.s_items.Add("合同起始日,T_bargains.StartDate,C");
wf.s_items.Add("合同终止日,T_bargains.EndDate,C");
wf.s_items.Add("合同终止张数,T_bargains.TerminalNum,N");
wf.s_items.Add("上门保养周期,T_bargains.MaintainGap,N");
wf.s_items.Add("响应速度,T_bargains.ResponseHour,N");
wf.s_items.Add("续保费,T_bargains.RenewalFee,N");
wf.s_items.Add("抄张周期,T_bargains.CopyNumGap,N");
wf.s_items.Add("签定类型,T_bargains.Addtype,C");
wf.s_items.Add("业务员,T_bargains.SaleUser,C");
wf.s_items.Add("备注,T_bargains.memo,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
initDataGridview(wf.Return_Sql);
}
}
private void btnFindMat_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select distinct T_MatInf.MatID 物料编号,MatName 物料名称," +
"Specifications 型号规格 ,Units 计量单位,Brand 品牌,ProductType 产品种类," +
"ColorType 色彩,Speed 速度等级,Format 幅面," +
"ynStopProduct 是否已停产,ConfigType 配置类型,purchaseprice 标准进货价 " +
"from T_MatInf,T_Mat_Rela "+
" where T_Mat_Rela.ParentMatID=T_MatInf.MatID ";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("品牌,Brand,C");
wf.s_items.Add("产品种类,ProductType,C");
wf.s_items.Add("标准进货价,purchaseprice,N");
wf.s_items.Add("配置类型,ConfigType,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
return;
this.comboBoxMatName.Text = wf.Return_Items[0].Trim();
}
}
private void button1_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "MatID", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格 ,Units 计量单位," +
"Brand 品牌,ProductType 产品种类,ColorType 色彩,Speed 速度等级,Format 幅面, ynStopProduct 是否已停产," +
"ConfigType 配置类型 " + "from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("拼音编码,PinYinCode,C");
wf.s_items.Add("品牌,Brand,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0)
{
MessageBox.Show("无对应结果!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.whereTJ = " where MatID='" + wf.Return_Items[0].Trim() + "'";
initDataGridView();
}
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "UserName", true);
wf.tableName = "T_users"; //表名
wf.strSql = "select UserId as 用户编码, UserName as 用户名, ynAdmin as 是否系统管理员,BranchId as 部门编码," +
"JobPosition as 职位, atGroup as 组别, DefaultUserType as 类别,SmsTel as 接收短信电话号码 " +
"from [T_Users] ";
wf.s_items.Add("用户编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("部门编码,BranchId,N");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("组别,atGroup,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List <string> sqls = new List <string>();
DBUtil dbUtil = new DBUtil();
string curUserId = this.userId;
foreach (string userName in wf.Return_Items)
{
string userId = dbUtil.Get_Single_val("T_Users", "UserId", "UserName", userName.Trim());
if (userId == "")
{
continue;
}
//插入前判断
string strSqlSel = "select * from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSqlSel = string.Format(strSqlSel, curUserId, userId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
{
continue;
}
string strSql = "insert into T_UserRelation(ParentPId,Pid) values('{0}','{1}')";
strSql = string.Format(strSql, curUserId, userId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
CmatnametextBox.Clear();
WFilter wf = new WFilter(1, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格 ,Units 计量单位," +
"Brand 品牌,ProductType 产品种类,ColorType 色彩,Speed 速度等级,Format 幅面,ynStopProduct 是否已停产," +
"ConfigType 配置类型,purchaseprice 标准进货价 " + "from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("拼音编码,PinYinCode,C");
wf.s_items.Add("品牌,Brand,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List <string> sqls = new List <string>();
DBUtil dbUtil = new DBUtil();
string curpmatid = this.pMatId;
foreach (string MatName in wf.Return_Items)
{
string curcmatid = dbUtil.Get_Single_val("T_MatInf", "MatID", "MatName", MatName.Trim());
if (curcmatid == "")
{
continue;
}
//插入前判断存在性和与与主物料的排斥性
string strSqlSel = "select * from T_Mat_Rela where ParentMatId='{0}' and ChildMatId='{1}'";
strSqlSel = string.Format(strSqlSel, curpmatid, curcmatid);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true || curpmatid == curcmatid.Trim())
{
continue;
}
string strSql = "insert into T_Mat_Rela(ParentMatId,ChildMatId) values('{0}','{1}')";
strSql = string.Format(strSql, curpmatid, curcmatid);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
initdataGridview();
}
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "CustName", true);
wf.tableName = "T_CustomerInf"; //表名
wf.strSql = "select CustID as 客户编号, CustName as 客户名称, CustType as 类别,City as 城市地区,communicateAddr as 通信地址, CredDegree as 信用等级,T_CustomerImp.importance as 客户重要度 " +
" from T_CustomerInf left join T_CustomerImp " +
" on T_CustomerInf.ImportanceDegreeId= T_CustomerImp.Iid";
wf.s_items.Add("客户编号,CustID,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("类别,CustType,C");
wf.s_items.Add("城市地区,City,C");
wf.s_items.Add("重要度,importance,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List <string> sqls = new List <string>();
DBUtil dbUtil = new DBUtil();
string custId = this.custid;
foreach (string CustName in wf.Return_Items)
{
string parentcustId = dbUtil.Get_Single_val("T_CustomerInf", "CustID", "CustName", CustName.Trim());
if (parentcustId == "")
{
return;
}
//插入前判断
string strSqlSel = "select * from T_Customer_Rela where CustID='{0}' and ParentID='{1}'";
strSqlSel = string.Format(strSqlSel, custId, parentcustId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
{
return;
}
string strSql = "insert into T_Customer_Rela(CustID,ParentID) values('{0}','{1}')";
strSql = string.Format(strSql, custId, parentcustId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "CustName", true);
wf.tableName = "T_CustomerInf"; //表名
wf.strSql = "select CustID as 客户编号, CustName as 客户名称, CustType as 类别,City as 城市地区,communicateAddr as 通信地址, CredDegree as 信用等级,T_CustomerImp.importance as 客户重要度 " +
" from T_CustomerInf left join T_CustomerImp " +
" on T_CustomerInf.ImportanceDegreeId= T_CustomerImp.Iid";
wf.s_items.Add("客户编号,CustID,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("类别,CustType,C");
wf.s_items.Add("城市地区,City,C");
wf.s_items.Add("重要度,importance,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List<string> sqls = new List<string>();
DBUtil dbUtil = new DBUtil();
string custId = this.custid ;
foreach (string CustName in wf.Return_Items)
{
string parentcustId = dbUtil.Get_Single_val("T_CustomerInf", "CustID", "CustName", CustName.Trim());
if (parentcustId == "")
return;
//插入前判断
string strSqlSel = "select * from T_Customer_Rela where CustID='{0}' and ParentID='{1}'";
strSqlSel = string.Format(strSqlSel, custId, parentcustId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
return;
string strSql = "insert into T_Customer_Rela(CustID,ParentID) values('{0}','{1}')";
strSql = string.Format(strSql, custId, parentcustId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "UserName", true);
wf.tableName = "T_users"; //表名
wf.strSql = "select UserId as 用户编码, UserName as 用户名, ynAdmin as 是否系统管理员,BranchId as 部门编码," +
"JobPosition as 职位, atGroup as 组别, DefaultUserType as 类别,SmsTel as 接收短信电话号码 " +
"from [T_Users] ";
wf.s_items.Add("用户编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("部门编码,BranchId,N");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("组别,atGroup,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List<string> sqls = new List<string>();
DBUtil dbUtil = new DBUtil();
string curUserId = this.userId;
foreach (string userName in wf.Return_Items)
{
string userId = dbUtil.Get_Single_val("T_Users", "UserId", "UserName", userName.Trim());
if (userId == "")
continue;
//插入前判断
string strSqlSel = "select * from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSqlSel = string.Format(strSqlSel, curUserId, userId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
continue;
string strSql = "insert into T_UserRelation(ParentPId,Pid) values('{0}','{1}')";
strSql = string.Format(strSql, curUserId, userId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
this.textBoxChildMatName.Clear();
WFilter wf = new WFilter(0, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格 ,Units 计量单位," +
"Brand 品牌,ProductType 产品种类,ColorType 色彩,Speed 速度等级,Format 幅面,ynStopProduct 是否已停产," +
"ConfigType 配置类型,purchaseprice 标准进货价 " + "from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("拼音编码,PinYinCode,C");
wf.s_items.Add("品牌,Brand,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
this.textBoxChildMatName.Text = wf.Return_Items[0];
}
}
private void button3_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "CustName", true);
wf.tableName = "T_customerInf"; //表名
wf.strSql = "select custid as 客户编码,CustName as 客户名称 from T_customerInf";
wf.s_items.Add("客户编码,CustId,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("联系人,Contact,C");
wf.s_items.Add("拼音助记码,PinYinCode,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count > 0)
{
T_CustID.Text = wf.Return_Items[0].Trim();
s_CustID.Text = (new DBUtil()).Get_Single_val("T_CustomerInf", "custid", "custname", T_CustID.Text.Trim());
}
}
}
private void button1_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "", true);
wf.strSql = this.BaseSql;
wf.btnOK.Visible = false;
wf.s_items.Add("客户编码,T_CustomerInf.Custid,C");
wf.s_items.Add("客户名称,T_CustomerInf.CustName,C");
wf.s_items.Add("机器地址,T_CustomerMac.Mdepart,C");
wf.s_items.Add("合同编号,T_bargains.BargId,C");
wf.s_items.Add("地区,T_AreaInf.Area,C");
wf.s_items.Add("机型,T_CustomerMac.Mtype,C");
wf.s_items.Add("机号,T_CustomerMac.Manufactcode,C");
wf.s_items.Add("预订技术员,T_CustomerMac.ptech,C");
wf.s_items.Add("预订业务员,T_CustomerMac.psale,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
Show_CustMat_Lst(wf.Return_Sql);
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
this.textBoxChildMatName.Clear();
WFilter wf = new WFilter(0, "MatName", true);
wf.tableName = "T_MatInf"; //表名
wf.strSql = "select T_MatInf.MatID 物料编号,MatName 物料名称,Specifications 型号规格 ,Units 计量单位,"+
"Brand 品牌,ProductType 产品种类,ColorType 色彩,Speed 速度等级,Format 幅面,ynStopProduct 是否已停产,"+
"ConfigType 配置类型,purchaseprice 标准进货价 "+ "from T_MatInf";
wf.s_items.Add("物料编号,MatID,C");
wf.s_items.Add("物料名称,MatName,C");
wf.s_items.Add("拼音编码,PinYinCode,C");
wf.s_items.Add("品牌,Brand,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
this.textBoxChildMatName.Text = wf.Return_Items[0];
}
}
private void button1_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(0, "", true);
wf.strSql = this.BaseSql;
wf.btnOK.Visible = false;
wf.s_items.Add("状态,T_bargains.Bargstatus,C");
wf.s_items.Add("合同类型,T_bargains.ContractType,C");
wf.s_items.Add("保修类别,tb_maintetype.maintetypename,C");
wf.s_items.Add("合同编号,T_bargains.BargId,C");
wf.s_items.Add("单位名称,T_CustomerInf.CustName,C");
wf.s_items.Add("机型,T_bargains.Mtype,C");
wf.s_items.Add("机号,T_bargains.Manufactcode,C");
wf.s_items.Add("机器地址,T_CustomerMac.Mdepart,C");
wf.s_items.Add("收费类型,T_bargains.FeeType,C");
wf.s_items.Add("合同起始日,T_bargains.StartDate,C");
wf.s_items.Add("合同终止日,T_bargains.EndDate,C");
wf.s_items.Add("合同终止张数,T_bargains.TerminalNum,N");
wf.s_items.Add("上门保养周期,T_bargains.MaintainGap,N");
wf.s_items.Add("响应速度,T_bargains.ResponseHour,N");
wf.s_items.Add("续保费,T_bargains.RenewalFee,N");
wf.s_items.Add("抄张周期,T_bargains.CopyNumGap,N");
wf.s_items.Add("签定类型,T_bargains.Addtype,C");
wf.s_items.Add("业务员,T_bargains.SaleUser,C");
wf.s_items.Add("备注,T_bargains.memo,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
initDataGridview(wf.Return_Sql);
}
}
/// <summary>
/// 初始化 单据子表
/// </summary>
public void InitDataGridViewDetail(int type)
{
this.progressBar1.Value = 0;
string receiptTypeID = "03";//单据模板编码(假设进货单)
string receiptId = this.txtReceiptId.Text.Trim();
string customerReceiptId = this.txtCustomerReceiptId.Text.Trim();
string occurTimeFrom = this.dtpFrom.Value.ToString().Trim();
string occurTimeTo = this.dtpTo.Value.ToString().Trim();
SortedList<int, string> listReceDetailItems;//单据子表项
listReceDetailItems = ReceiptModalCfgDAO.GetShowItems(receiptTypeID, 1, -1);
//构造Sql
string strSql = "select T_Receipts_Det.ReceiptId as 单据号,T_Receipt_Main.CustomerReceiptNo as 自定义单据号, T_Receipts_Det.OrderNo as 顺序号, "+
"CustName as 客户名称, OccurTime as 单据日期, T_MatInf.MatName as 物料名称, ";
string fileds = "";
if (listReceDetailItems != null)
{
foreach (string str in listReceDetailItems.Values)
{
string filedTemp = ReceiptModCfg.GetReceiptDetailItems()[str.Trim()].Trim().Substring(2);
if (filedTemp == "MatId")
filedTemp = "T_Receipts_Det." + filedTemp;
fileds += filedTemp + " as " + str + ",";
}
}
if (fileds == "")
return;
fileds = fileds.Remove(fileds.Length - 1);
strSql += fileds + " from T_Receipt_Main,T_Receipts_Det,T_MatInf " +
" where T_Receipt_Main.ReceiptTypeID='{0}' and OccurTime > '2003-12-31' and " + //2003-12-31之前不处理未核销
" T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId=T_MatInf.MatID ";
strSql = string.Format(strSql, receiptTypeID);
if (type == 1) //未核销查询
{
if (receiptId != "")
{
strSql += " and T_Receipts_Det.ReceiptId='" + receiptId + "'";
}
if (customerReceiptId != "")
{
strSql += " and T_Receipt_Main.CustomerReceiptNo='" + customerReceiptId + "'";
}
strSql += " and OccurTime between '{0}' And '{1}'";
strSql = string.Format(strSql, occurTimeFrom, occurTimeTo);
}
else if (type == 0) //高级查找
{
//添加查找窗体
WFilter wf = new WFilter(0, "单据号", false);
wf.strSql = strSql;
wf.s_items.Add("单据号,T_Receipts_Det.ReceiptId,C");
wf.s_items.Add("自定义单据号,T_Receipt_Main.CustomerReceiptNo,C");
wf.s_items.Add("客户编码,CustId,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("单据日期,OccurTime,N");
wf.s_items.Add("物料编码,T_Receipts_Det.MatId,C");
wf.s_items.Add("物料名称,T_MatInf.MatName,C");
wf.btnOK.Enabled = false;
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//返回条件框中的sql语句
strSql = wf.Return_Sql;
int index = strSql.IndexOf(" where ");
if (!strSql.Substring(index + 6).Contains("OccurTime "))
{
strSql += " and (OccurTime between '{0}' And '{1}')";
strSql = string.Format(strSql, occurTimeFrom, occurTimeTo);
}
}
else
return;
}
strSql += " order by OccurTime"; //排序
DataTable dt = (new SqlDBConnect()).Get_Dt(strSql);
(new InitFuncs()).InitDataGridView(this.dgvReceipt_Det, dt);
this.progressBar1.Minimum = 0;
this.progressBar1.Maximum = dt.Rows.Count;
try
{
List<DataGridViewRow> deleteRows = new List<DataGridViewRow>();
//更新 未核销的商品数量
foreach (DataGridViewRow dgvr in this.dgvReceipt_Det.Rows)
{
string receiptId03 = "";
int orderNo03 = 0;
string matId = "";
int matType = 0;
double price = 0;
int num = 0;
if (dgvr.Cells["单据号"].Value == null || dgvr.Cells["单据号"].Value.ToString().Trim() == "")
continue;
if (dgvr.Cells["顺序号"].Value == null || dgvr.Cells["顺序号"].Value.ToString().Trim() == "")
continue;
if (dgvr.Cells["物料编码"].Value == null || dgvr.Cells["物料编码"].Value.ToString().Trim() == "")
continue;
if (dgvr.Cells["类型"].Value == null || dgvr.Cells["类型"].Value.ToString().Trim() == "")
continue;
if (dgvr.Cells["单价"].Value == null || dgvr.Cells["单价"].Value.ToString().Trim() == "")
continue;
if (dgvr.Cells["数量"].Value == null || dgvr.Cells["数量"].Value.ToString().Trim() == "")
continue;
receiptId03 = dgvr.Cells["单据号"].Value.ToString().Trim();
orderNo03 = Convert.ToInt32(dgvr.Cells["顺序号"].Value.ToString().Trim());
num = Convert.ToInt32(dgvr.Cells["数量"].Value.ToString().Trim());
matId = dgvr.Cells["物料编码"].Value.ToString().Trim();
matType = Convert.ToInt32(dgvr.Cells["类型"].Value.ToString().Trim());
price = Convert.ToDouble(dgvr.Cells["单价"].Value.ToString().Trim());
UpdateNotVerificateMatNum(receiptId03, orderNo03, num, price, dgvr, ref deleteRows);
this.progressBar1.Value++;
}
if (this.checkBox1.Checked == false)
{
//删除已冲销完的行
foreach (DataGridViewRow row in deleteRows)
{
this.dgvReceipt_Det.Rows.Remove(row);
}
}
this.progressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
private void button6_Click(object sender, EventArgs e)
{
try
{
WFilter wf = new WFilter(0, "UserName", true);
wf.StartPosition = FormStartPosition.CenterScreen;
wf.tableName = "T_users"; //表名
wf.strSql = "select UserId as 用户编码, UserName as 用户名, ynAdmin as 是否系统管理员,BranchId as 部门编码," +
"JobPosition as 职位, atGroup as 组别, DefaultUserType as 类别,SmsTel as 接收短信电话号码 " +
"from [T_Users] ";
wf.s_items.Add("编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("组别,atGroup,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
this.comboBoxUserName.Text = wf.Return_Items[0].Trim();
}
}
catch
{
MessageBox.Show("请正确操作!", "提示");
}
}
/// <summary>
/// Label的响应事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void label_Click(object sender, EventArgs e)
{
string columnName = (sender as Label).Name;
switch (columnName)
{
#region case段
case "商品编号":
{
string strMatId = InfoFind.Find_MatId();
DataBindMatInf(strMatId, 0);//绑定
break;
}
case "数量":
{
Label label = sender as Label;
TempInStoreForm form = new TempInStoreForm();
form.Location = new Point(MousePosition.X, MousePosition.Y);
form.ShowDialog();
break;
}
case "客户单位名称":
case "供应商名称":
case "客户单位编号":
case "供应商编号":
{
#region //筛选
WFilter wf = new WFilter(0, "T_CustomerInf.CustID", true);
wf.tableName = "T_CustomerInf"; //表名
wf.strSql = "select T_CustomerInf.CustID as 客户编号, T_CustomerInf.CustName as 客户名称, T_CustomerInf.CustType as 类别," +
"T_CustomerInf.PinYinCode as 拼音助记码,T_CustomerInf.whichTrade as 所在行业, T_CustomerInf.City as 城市地区,T_CustomerInf.Province as 省," +
"T_CustContacts.CName as 联系人,T_CustContacts.Tel as 联系电话,T_CustomerInf.communicateAddr as 通信地址," +
"T_CustomerInf.BankAccount as 银行帐号, T_CustomerInf.CredDegree as 信用等级, T_CustomerInf.InvoiceTitle as 发票抬头 " +
"from T_CustomerInf,T_CustContacts " +
"where T_CustomerInf.CustID=T_CustContacts.CustID and T_CustContacts.CType='默认联系人'";
wf.s_items.Add("客户编号,T_CustomerInf.CustID,C");
wf.s_items.Add("客户名称,T_CustomerInf.CustName,C");
wf.s_items.Add("通信地址,communicateAddr,C");
wf.s_items.Add("联系人,T_CustContacts.CName,C");
wf.s_items.Add("联系电话,Tel,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
string strCustID = "";
if (wf.Return_Items.Count > 0)
strCustID = wf.Return_Items[0].Trim();//获得客户单位编号
DataBindCustomerInf(strCustID);//绑定
}
#endregion
break;
}
case "发票类型":
{
#region //筛选
WFilter wf = new WFilter(0, "ITName", true);
wf.tableName = "T_Invoice"; //表名
wf.strSql = "select ITCode as 发票类型编号,ITName as 发票类型名,TaxRate as 税率 " +
"from T_Invoice";
wf.s_items.Add("发票类型编号,ITCode,C");
wf.s_items.Add("发票类型名,ITName,C");
wf.s_items.Add("税率,TaxRate,N");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0) //没有数据
return;
((sender as Label).Parent as TextBox).Text = wf.Return_Items[0].Trim();//获得发票类型名
}
#endregion
break;
}
case "仓库":
case "目标仓库":
{
#region //筛选
WFilter wf = new WFilter(0, "SHName", true);
wf.tableName = "T_StoreHouse"; //表名
wf.strSql = "select T_StoreHouse.SHId as 仓库编号,T_StoreHouse.SHName as 仓库名," +
"T_StoreHouse.SHKeeper as 库管员编号,T_StoreHouse.SHAddr as 仓库地址," +
"T_StoreHouse.Tel as 电话,T_StoreHouse.Fax as 传真,T_StoreHouse.NetAddr 网络地址," +
"T_StoreHouse.Storememo as 备注 from T_StoreHouse";
wf.s_items.Add("仓库编号,SHId,C");
wf.s_items.Add("仓库名,SHName,C");
wf.s_items.Add("库管员编号,SHKeeper,C");
wf.s_items.Add("仓库地址,SHAddr,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0) //没有数据
return;
((sender as Label).Parent as TextBox).Text = wf.Return_Items[0].Trim();//获得仓库名称
}
#endregion
break;
}
case "送货人":
case "收货人":
case "业务员":
case "技术员":
case "验收员":
case "保管员":
case "制单人":
case "收款人":
case "复核员":
{
#region //筛选
WFilter wf = new WFilter(0, "UserName", true);
wf.tableName = "T_users"; //表名
wf.strSql = "select distinct T_Users.UserId as 用户编码, T_Users.UserName as 用户名, T_Users.ynAdmin as 是否系统管理员,T_Branch.BName as 所属部门," +
"T_Users.JobPosition as 职位,T_UserType.UTypeName as 类别,T_Users.SmsTel as 接收短信电话号码 " +
"from [T_Users] left join T_Branch " +
"on T_Users.BranchId=T_Branch.BId left join T_UserType on T_Users.DefaultUserType=T_UserType.TypeId";
wf.s_items.Add("用户编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("所属部门,BName,C");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("类别,UTypeName,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
if (wf.Return_Items.Count <= 0) //没有数据
return;
((sender as Label).Parent as TextBox).Text = wf.Return_Items[0].Trim();//获得用户名称
}
#endregion
break;
}
default:
break;
#endregion
}
}
/// <summary>
/// 初始化 单据子表
/// </summary>
public void InitDataGridViewDetail(int type)
{
this.progressBar1.Value = 0;
string receiptTypeID = "03";//单据模板编码(假设进货单)
string receiptId = this.txtReceiptId.Text.Trim();
string customerReceiptId = this.txtCustomerReceiptId.Text.Trim();
string occurTimeFrom = this.dtpFrom.Value.ToString().Trim();
string occurTimeTo = this.dtpTo.Value.ToString().Trim();
SortedList <int, string> listReceDetailItems;//单据子表项
listReceDetailItems = ReceiptModalCfgDAO.GetShowItems(receiptTypeID, 1, -1);
//构造Sql
string strSql = "select T_Receipts_Det.ReceiptId as 单据号,T_Receipt_Main.CustomerReceiptNo as 自定义单据号, T_Receipts_Det.OrderNo as 顺序号, " +
"CustName as 客户名称, OccurTime as 单据日期, T_MatInf.MatName as 物料名称, ";
string fileds = "";
if (listReceDetailItems != null)
{
foreach (string str in listReceDetailItems.Values)
{
string filedTemp = ReceiptModCfg.GetReceiptDetailItems()[str.Trim()].Trim().Substring(2);
if (filedTemp == "MatId")
{
filedTemp = "T_Receipts_Det." + filedTemp;
}
fileds += filedTemp + " as " + str + ",";
}
}
if (fileds == "")
{
return;
}
fileds = fileds.Remove(fileds.Length - 1);
strSql += fileds + " from T_Receipt_Main,T_Receipts_Det,T_MatInf " +
" where T_Receipt_Main.ReceiptTypeID='{0}' and OccurTime > '2003-12-31' and " + //2003-12-31之前不处理未核销
" T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId=T_MatInf.MatID ";
strSql = string.Format(strSql, receiptTypeID);
if (type == 1) //未核销查询
{
if (receiptId != "")
{
strSql += " and T_Receipts_Det.ReceiptId='" + receiptId + "'";
}
if (customerReceiptId != "")
{
strSql += " and T_Receipt_Main.CustomerReceiptNo='" + customerReceiptId + "'";
}
strSql += " and OccurTime between '{0}' And '{1}'";
strSql = string.Format(strSql, occurTimeFrom, occurTimeTo);
}
else if (type == 0) //高级查找
{
//添加查找窗体
WFilter wf = new WFilter(0, "单据号", false);
wf.strSql = strSql;
wf.s_items.Add("单据号,T_Receipts_Det.ReceiptId,C");
wf.s_items.Add("自定义单据号,T_Receipt_Main.CustomerReceiptNo,C");
wf.s_items.Add("客户编码,CustId,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("单据日期,OccurTime,N");
wf.s_items.Add("物料编码,T_Receipts_Det.MatId,C");
wf.s_items.Add("物料名称,T_MatInf.MatName,C");
wf.btnOK.Enabled = false;
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//返回条件框中的sql语句
strSql = wf.Return_Sql;
int index = strSql.IndexOf(" where ");
if (!strSql.Substring(index + 6).Contains("OccurTime "))
{
strSql += " and (OccurTime between '{0}' And '{1}')";
strSql = string.Format(strSql, occurTimeFrom, occurTimeTo);
}
}
else
{
return;
}
}
strSql += " order by OccurTime"; //排序
DataTable dt = (new SqlDBConnect()).Get_Dt(strSql);
(new InitFuncs()).InitDataGridView(this.dgvReceipt_Det, dt);
this.progressBar1.Minimum = 0;
this.progressBar1.Maximum = dt.Rows.Count;
try
{
List <DataGridViewRow> deleteRows = new List <DataGridViewRow>();
//更新 未核销的商品数量
foreach (DataGridViewRow dgvr in this.dgvReceipt_Det.Rows)
{
string receiptId03 = "";
int orderNo03 = 0;
string matId = "";
int matType = 0;
double price = 0;
int num = 0;
if (dgvr.Cells["单据号"].Value == null || dgvr.Cells["单据号"].Value.ToString().Trim() == "")
{
continue;
}
if (dgvr.Cells["顺序号"].Value == null || dgvr.Cells["顺序号"].Value.ToString().Trim() == "")
{
continue;
}
if (dgvr.Cells["物料编码"].Value == null || dgvr.Cells["物料编码"].Value.ToString().Trim() == "")
{
continue;
}
if (dgvr.Cells["类型"].Value == null || dgvr.Cells["类型"].Value.ToString().Trim() == "")
{
continue;
}
if (dgvr.Cells["单价"].Value == null || dgvr.Cells["单价"].Value.ToString().Trim() == "")
{
continue;
}
if (dgvr.Cells["数量"].Value == null || dgvr.Cells["数量"].Value.ToString().Trim() == "")
{
continue;
}
receiptId03 = dgvr.Cells["单据号"].Value.ToString().Trim();
orderNo03 = Convert.ToInt32(dgvr.Cells["顺序号"].Value.ToString().Trim());
num = Convert.ToInt32(dgvr.Cells["数量"].Value.ToString().Trim());
matId = dgvr.Cells["物料编码"].Value.ToString().Trim();
matType = Convert.ToInt32(dgvr.Cells["类型"].Value.ToString().Trim());
price = Convert.ToDouble(dgvr.Cells["单价"].Value.ToString().Trim());
UpdateNotVerificateMatNum(receiptId03, orderNo03, num, price, dgvr, ref deleteRows);
this.progressBar1.Value++;
}
if (this.checkBox1.Checked == false)
{
//删除已冲销完的行
foreach (DataGridViewRow row in deleteRows)
{
this.dgvReceipt_Det.Rows.Remove(row);
}
}
this.progressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}