private void butFind_Click(object sender, EventArgs e)
{
//查询单据
string sSql = "";
string strAreaid = gdFindWhere.Cell(8, 1).Text.ToString(); //区域
sSql = " select a.*,d.custname ,d.telname ,d.tel,d.ddtel,d.address,d.city,d.cityid,d.khwxh ";
sSql += " from Inv_Evi a inner join dbo.f_CAreaid('" + strAreaid + "') c on a.areaid =c.AreaID ";
sSql += " inner join customer d on a.custid=d.custid ";
sSql += " where 1=1 ";
if (gdFindWhere.Cell(1, 1).Text.ToString() == "单据日期")
{
sSql += " and a.evidate>='" + gdFindWhere.Cell(2, 1).Text.ToString() + "' and a.evidate <='" + gdFindWhere.Cell(3, 1).Text.ToString() + "'";
}
else if (gdFindWhere.Cell(1, 1).Text.ToString() == "审核日期")
{
sSql += " and a.Verifierdate>='" + gdFindWhere.Cell(2, 1).Text.ToString() + "' and a.Verifierdate <='" + gdFindWhere.Cell(3, 1).Text.ToString() + "'";
}
//条件，快递
if (gdFindWhere.Cell(4, 1).Text.ToString() == "1")
{
sSql += " and ISNULL(a.ISKD,0)='1'";
}
else
{
sSql += " and ISNULL(a.ISKD,0)='0'";
}
SqlDataReader rd = DbHelperSQL.ExecuteReader(sSql);
int intR = 1;
try
{
while (rd.Read())
{
intR += 1;
gdFindList.Rows = intR;
gdFindList.Cell(intR - 1, 1).Text = ""; //选择
gdFindList.Cell(intR - 1, 2).Text = rd["EviNo"].ToString(); //单据编号
gdFindList.Cell(intR - 1, 3).Text = PubFun.Nz(rd["isPCFH"].ToString(), ""); //是否排产
gdFindList.Cell(intR - 1, 4).Text = rd["isKD"].ToString(); //是否快递
gdFindList.Cell(intR - 1, 5).Text = rd["KDDH"].ToString(); //快递单号
gdFindList.Cell(intR - 1, 6).Text = rd["driver"].ToString(); //司机
gdFindList.Cell(intR - 1, 7).Text = rd["khwxh"].ToString(); //客户微信号
gdFindList.Cell(intR - 1, 8).Text = rd["CustName"].ToString(); //客户名称
gdFindList.Cell(intR - 1, 8).WrapText = true; //换行
gdFindList.Cell(intR - 1, 8).Tag = rd["Custid"].ToString(); //客户编号
gdFindList.Cell(intR - 1, 9).Text = rd["SumWeight"].ToString(); //重量
gdFindList.Cell(intR - 1, 10).Text = rd["SumnQty"].ToString(); //件数
gdFindList.Cell(intR - 1, 11).Text = rd["telname"].ToString(); //联系人
gdFindList.Cell(intR - 1, 12).Text = rd["Tel"].ToString(); //手机
gdFindList.Cell(intR - 1, 13).Text = rd["DDTel"].ToString(); //固话
if (rd["cityID"].ToString().Substring(1, 2) == "44")
{
gdFindList.Cell(intR - 1, 14).Text = "广东省" + rd["Address"].ToString(); //广东地址要加上"广东省"
}
else
{
gdFindList.Cell(intR - 1, 14).Text = rd["Address"].ToString();
}
gdFindList.Cell(intR - 1, 14).WrapText = true;//换行
}
}
catch { }
}
private void butFind_Click(object sender, EventArgs e)
{
string sSql = " ";
string sItem = " ";
// AreaID,AreaName,PrevID
sSql = "select h.* ";
sSql += ",o.orderStatus,u.isWXFocus,o.CustWXnum ";
sSql = sSql + " ,case o.orderStatus ";
sSql = sSql + " when -10 then '已取消'";
sSql = sSql + " when 10 then '已完成'";
sSql = sSql + " when 11 then '未付款待发货'";
sSql = sSql + " when 12 then '未付款已受理'";
sSql = sSql + " when 13 then '未付款正在备货'";
sSql = sSql + " when 14 then '未付款已发货'";
sSql = sSql + " when 15 then '未付款已签收'";
sSql = sSql + " when 21 then '已付款待发货'";
sSql = sSql + " when 22 then '已付款已受理'";
sSql = sSql + " when 23 then '已付款正在备货'";
sSql = sSql + " when 24 then '已付款已发货'";
sSql = sSql + " Else ''";
sSql = sSql + " end orderStatusName";
sSql += " from inv_evi h ";
sSql += " left join DefWeixin.dbo.WxOrder o on h.evino = o.orderno ";
sSql += " left join DefWeixin.dbo.WxUserCust u on o.CustWXnum = u.openid ";
if (gdFindWhere.Cell(1, 1).Text == "单据日期")
{
sSql += " where h.evidate between '" + gdFindWhere.Cell(2, 1).Text + "' and '" + gdFindWhere.Cell(3, 1).Text + "' ";
}
else //审核日期
{
sSql += " where h.Verifierdate between '" + gdFindWhere.Cell(2, 1).Text + "' and '" + gdFindWhere.Cell(3, 1).Text + "' ";
}
if (gdFindWhere.Cell(4, 1).Text == "已审核") //单据状态
{
sSql += " and h.Verified='1' ";
}
else if (gdFindWhere.Cell(4, 1).Text == "未审核") //单据状态
{
sSql += " and h.Verified<>'1' ";
}
//客户编号
if (gdFindWhere.Cell(7, 1).Text != "")
{
sSql += " and (h.custid like '%" + gdFindWhere.Cell(7, 1).Text + "%' or h.custname like '%" + gdFindWhere.Cell(7, 1).Text + "%' ) ";
}
//确认快递
if (gdFindWhere.Cell(9, 1).Text == "1")
{
sSql += " and h.iskd=1 ";
}
DataSet ds = DbHelperSQL.Query(sSql);
//SqlDataReader dr
//SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
gdFindList.Rows = 1;
gdFindList.AutoRedraw = false;
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sItem = "";
try
{
sItem += "\t" + ds.Tables[0].Rows[i]["evino"].ToString().Trim(); //dr["evino"].ToString();
//是否排产 PCBZ
sItem += "\t " + ds.Tables[0].Rows[i]["PCBZ"].ToString();
sItem += "\t" + ds.Tables[0].Rows[i]["iskd"].ToString(); // dr["iskd"].ToString(); //是否快递
sItem += "\t" + ds.Tables[0].Rows[i]["Verified"].ToString(); //dr["Verified"].ToString(); //是否 审批
sItem += "\t" + ds.Tables[0].Rows[i]["payed"].ToString(); //dr["payed"].ToString(); //是否 付款
sItem += "\t" + ds.Tables[0].Rows[i]["evidate"].ToString().Substring(0, 10); // dr["evidate"].ToString().Substring(0, 10); // 单据日期
sItem += "\t" + ds.Tables[0].Rows[i]["driver"].ToString(); // dr["driver"].ToString(); //司机
// var wxno = dr["Wxorderno"].ToString();
//sItem += "\t" + ds.Tables[0].Rows[i]["Wxorderno"] == null ? "" : ds.Tables[0].Rows[i]["Wxorderno"].ToString(); //, PubFun.Nz(dr["Wxorderno"].ToString(), "");
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["Wxorderno"], ""); //微信订单号
//微信订单状态
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["orderStatus"], "") + "|" + PubFun.Nz(ds.Tables[0].Rows[i]["orderStatusName"], "");
//微信号 CustWXnum
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["CustWXnum"], "");
//是否取消关注 isWXFocus -1 已取消
if (ds.Tables[0].Rows[i]["isWXFocus"].ToString() == "-1")
{
sItem += "\t已取消";
}
else
{
sItem += "\t";
}
//客户
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["custid"], "") + "|" + PubFun.Nz(ds.Tables[0].Rows[i]["custname"], "");
//单据 重量
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["SumWeight"], "");
//单据 罐数
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["SumQty"], "");
//单据 件数
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["SumNQty"], "");
//单据 金额
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["SumAmount"], "");
//单据 联系人
sItem += "\t";
//单据 手机
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["tel"], "");
//单据 固话
sItem += "\t";
//单据 地址
sItem += "\t" + PubFun.Nz(ds.Tables[0].Rows[i]["caddress"], "");
}
catch (Exception ex)
{
MessageBox.Show("提示！:" + ex);
break;
}
gdFindList.AddItem(sItem);
//标识已取消关注为红色
gdFindList.Cell(gdFindList.Rows - 1, 12).ForeColor = Color.Red;
//客户换行
gdFindList.Cell(gdFindList.Rows - 1, 13).WrapText = true;
//客户手机
gdFindList.Cell(gdFindList.Rows - 1, 19).WrapText = true;
//客户地址
gdFindList.Cell(gdFindList.Rows - 1, 21).WrapText = true;
}
}
gdFindList.AutoRedraw = true;
gdFindList.Refresh();
//while (dr.Read())
//{
// sItem = "";
// // string getID = dr["lsbh"].ToString();
// try
// {
// //ds.Tables[0].Rows[i].ItemArray[0].ToString().Trim();
// sItem += "\t" + dr["evino"].ToString();
// sItem += "\t " ; //是否排产
// sItem += "\t" + dr["iskd"].ToString() ; //是否快递
// sItem += "\t" + dr["Verified"].ToString(); //是否 审批
// sItem += "\t" + dr["payed"].ToString(); //是否 付款
// sItem += "\t" + dr["evidate"].ToString().Substring(0,10) ; // 单据日期
// sItem += "\t" + dr["driver"].ToString(); //司机
// var wxno = dr["Wxorderno"].ToString();
// sItem += "\t" + PubFun.Nz(dr["Wxorderno"].ToString(),"");
// //if(dr["Wxorderno"].ToString().IsNullOrEmpty())
// //{
// // sItem += "\t";
// //}
// //else
// //{
// // sItem += "\t" + dr["Wxorderno"].ToString();
// //}
// //if(dr["Wxorderno"].ToString()==null) //微信订单号
// //{
// // sItem += "\t" ;
// //}
// //else
// //{
// // sItem += "\t" + dr["Wxorderno"].ToString();
// //}
// gdFindList.AddItem(sItem);
// }
// catch (Exception ex)
// {
// MessageBox.Show("提示！:" + ex);
// break;
// }
//}
}
