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; // } //} }