下载业务员 select top 200 * from (select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar,uprecndate,120) as uprecndate from company where area_id='0000' and company_no in (select company_h from company where company_no in (select area_no from compright where suser_id='" + tSaleId.Trim() + "')) and compkind='区域' union all select busikind,company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar,uprecndate,120) as uprecndate from company where company_no in (select area_no from compright where suser_id=' + tSaleId.Trim() + ') and compkind='区域' and area_id<>'0000' union all select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar,uprecndate,120) as uprecndate from company where company_h in (select area_no from compright where suser_id='" + tSaleId.Trim() + "') and compkind='零售商') as a where convert(varchar,uprecndate,120) > tCondition order by uprecndate keyfiled 新的 select * from (select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,keyfiled as keyfiled from company where area_id='0000' and company_no in (select company_h from company where company_no in (select area_no from compright where suser_id='0001')) and compkind='区域' union all select busikind,company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,keyfiled as keyfiled from company where company_no in (select area_no from compright where suser_id='0001') and compkind='区域' and area_id<>'0000' union all select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,keyfiled as keyfiled from company where company_h in (select area_no from compright where suser_id='0001') and compkind='零售商') as a order by keyfiled select * from (select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where area_id='0000' and company_no in (select company_h from company where company_no in (select area_no from compright where suser_id='test003')) and compkind='区域' union all select busikind,company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where company_no in (select area_no from compright where suser_id='test003') and compkind='区域' and area_id<>'0000' union all select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where company_h in (select area_no from compright where suser_id='test003') and compkind='零售商') as a order by keyfiled 加上直营区 select * from (select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where area_id='0000' and company_no in (select company_h from company where company_no in (select area_no from compright where suser_id='test003')) and compkind='区域' union all select busikind,company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where company_no in (select area_no from compright where suser_id='test003') and compkind='区域' and area_id<>'0000' union all select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where company_h in (select area_no from compright where suser_id='test003') and compkind='零售商' union all select busikind, company_id,company_na,link,tel,corpaddr,disc,brand_name,cumspec,belarea_name,pricgrade,fax,mobile,compkind,area_name,area_id, suser_id,company_no,company_h,convert(varchar(30),convert(datetime,keyfiled,121),121) as keyfiled from company where company_id in (select suser_id from company where company_no in (select area_no from compright where suser_id='test003')) and compkind = '代理商' ) as a order by keyfiled 考勤 attence select * from salevisit_photo select * from sale_promotion delete from sale_promotion where recid < 8 DM_0001_20140315101534.jpg select * from sale_vieproduct --查看今天打卡人数 select * from attence where convert(varchar(10),punchdate,120) = convert(varchar(10),getdate(),120) 1.进货汇总 P_AnTwoCompanyInSum 零售商代号 日期区间 型号 ‘全部' 色号 '全部' 显示: 型号-色号 数量 modelcolor sendqty A单男款 数量 A单女款 数量 B单男款 数量 B单女款 数量 2.退货汇总 P_AnTwoCompanyBackSum 零售商代号 日期区间 型号 ‘全部' 色号 '全部' 显示: 型号-色号 数量 modelcolor backqty A单男款 数量 A单女款 数量 B单男款 数量 B单女款 数量 3.门店查询 P_AnTwoCompanyAdReSum 参数：零售商代号 3.1.明细表 参数：零售商代号 (1).进货及退货明细:P_AnTwoCompanyAdDetail 显示: 参数：零售商代号 3.2 (2).退货明细:P_AnTwoCompanyReDetail 显示: 存储过程 1、--拜访项目 P_MarkVisitKind 促销执行只能提交一个 pc 竞品提报 第一张图片不显示的问题 company 添加新字段 keyfiled timestamp 修改接口 （改用Json传数据） BarcodeLogistics //物流查询 [WebMethod(true)] public String BarcodeLogistics(string tLoginId, string tCode, bool tBcOrAc, string tCompnay) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryWinning = new string[] { "memo" }; if (dtWork.GetDataSet(string.Format("exec P_ProductLogist '{0}','{1}','{2}'", (tBcOrAc ? tCode : ""), (tBcOrAc ? "" : tCode), ""), "temp", ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables["temp"].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[0].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryWinning[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //防伪码积分记录查询 [WebMethod(true)] public string GetAcodeWinningRecno(string tLoginId, string tAcode, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryWinning = new string[] { "memo"}; if (dtWork.GetDataSet("exec P_AnAcodeWinning '" + tAcode.Trim() + "'", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryWinning[i], null)).InnerText = Convert.ToString(row[i]); } } retResult.Success = "true"; retResult.Content = document.OuterXml; return retResult.ToString(); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //手机号积分累计已兑查询 [WebMethod(true)] public string GetMobileWinningSum(string tLoginId, string tMobile, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryWinning = new string[] { "winning", "okconvertwinning", "noconvertwinning" }; if (dtWork.GetDataSet("select winning,convertwinning as okconvertwinning,winning-convertwinning as noconvertwinning from clerk where mobile='" + tMobile.Trim() + "'", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryWinning[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //手机号积分明细查询 [WebMethod(true)] public string GetMobileWinningDetail(string tLoginId, string tMobile, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryWinning = new string[] { "lookdate", "acode", "winning" }; if (dtWork.GetDataSet("select lookdate,acode,winning from captcha..smlook smlook where looktel='" + tMobile.Trim() + "' order by lookdate", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryWinning[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg( document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //下载零售商进货汇总 [WebMethod(true)] public string GetTwoCompanyTradeData1(string tLoginId, string tTwoCompanyId, string cStartDate, string cEndDate,string cModelm,string cColors) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryTwoCompany = new string[] { "inaboy", "inagirl", "inbboy", "inbgirl", "backaboy", "backagirl", "backbboy", "backbgirl" }; //cStartDate = "1990-01-01 00:00:00"; //cEndDate = "2014_03-12 00:00:00"; if (dtWork.GetDataSet("exec P_AnTwoCompanyAdReSum '" + tTwoCompanyId.Trim() + "','" + cStartDate + "','" + cEndDate + "','" + cModelm + "','" + cColors + "'", "temp", ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[0].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[0].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //退货 ---> 退货汇总 [WebMethod(true)] public string P_AnTwoCompanyBackSum1(string tLoginId, string tTwoCompanyId, string cStartDate, string cEndDate, string cModelm, string cColors) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryTwoCompany = new string[] { "inaboy", "inagirl", "inbboy", "inbgirl", "backaboy", "backagirl", "backbboy", "backbgirl" }; if (dtWork.GetDataSet("exec P_AnTwoCompanyBackSum '" + tTwoCompanyId.Trim() + "','" + cStartDate + "','" + cEndDate + "','" + cModelm + "','" + cColors + "'", "temp", ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[0].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[0].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //下载零售商进货汇总 [WebMethod(true)] public string GetTwoCompanyTradeData(string tLoginId, string tTwoCompanyId, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryTwoCompany = new string[] { "inaboy", "inagirl", "inbboy", "inbgirl", "backaboy", "backagirl", "backbboy", "backbgirl" }; if (dtWork.GetDataSet("exec P_AnTwoCompanyAdReSum '" + tTwoCompanyId.Trim() + "'", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //门店查询----进货明细 //下载零售商进货明细函数 [WebMethod(true)] public string GetTwoCompanyAdDetail(string tLoginId, string tTwoCompanyId, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryTwoCompany = new string[] { "modelm", "qty"}; if (dtWork.GetDataSet("exec P_AnTwoCompanyAdDetail '" + tTwoCompanyId.Trim() + "'", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); } } return retResult.OkMsg(document.OuterXml); } else { return retResult.ErrorMsg(dtWork.Errmsg.Trim()); } } catch (Exception ex) { return retResult.ErrorMsg(ex.Message.Trim()); } } //门店查询----退货明细 //下载零售商退货明细函数 [WebMethod(true)] public string GetTwoCompanyReDetail(string tLoginId, string tTwoCompanyId, string tTableName) { if (checkUserLoginTime(tLoginId)) { return retResult.ErrorMsg("用户登录已过期，请重新登录。"); } try { DataSet mDts = new DataSet(); string[] cArryTwoCompany = new string[] { "modelm", "qty" }; //if (true) //{ // DataTable dt = new DataTable(tTableName); // dt.Columns.Add("col1", typeof(String)); // dt.Columns.Add("col2", typeof(String)); // mDts.Tables.Add(dt); // DataRow dr = dt.NewRow(); // dr[0] = "BL9088-C01"; // dr[1] = "100"; // dt.Rows.Add(dr); // dr = dt.NewRow(); // dr[0] = "BL9088-C02"; // dr[1] = "300"; // dt.Rows.Add(dr); // dr = dt.NewRow(); // dr[0] = "BL9088-C03"; // dr[1] = "500"; // dt.Rows.Add(dr); // dr = dt.NewRow(); // dr[0] = "BL9088-C05"; // dr[1] = "200"; // dt.Rows.Add(dr); // XmlDocument document = new XmlDocument(); // document.CreateXmlDeclaration("1.0", "utf-8", null); // XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); // document.AppendChild(newChild); // XmlNode node2 = null; // DataRow row = null; // foreach (DataRow row2 in mDts.Tables[tTableName].Rows) // { // row = row2; // node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); // newChild.AppendChild(node2); // for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) // { // node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); // } // } // retResult.Success = "true"; // retResult.Content = document.OuterXml; // return retResult.ToString(); //} //else //{ // retResult.Success = "false"; // retResult.Content = dtWork.Errmsg.Trim(); // return retResult.ToString(); //} //mov by cai 2014-01-28 if (dtWork.GetDataSet("exec P_AnTwoCompanyReDetail '" + tTwoCompanyId.Trim() + "'", tTableName, ref mDts)) { XmlDocument document = new XmlDocument(); document.CreateXmlDeclaration("1.0", "utf-8", null); XmlNode newChild = document.CreateNode(XmlNodeType.Element, "ParamentList", null); document.AppendChild(newChild); XmlNode node2 = null; DataRow row = null; foreach (DataRow row2 in mDts.Tables[tTableName].Rows) { row = row2; node2 = document.CreateNode(XmlNodeType.Element, "Paraments", null); newChild.AppendChild(node2); for (int i = 0; i <= (mDts.Tables[tTableName].Columns.Count - 1); i++) { node2.AppendChild(document.CreateNode(XmlNodeType.Element, cArryTwoCompany[i], null)).InnerText = Convert.ToString(row[i]); } } retResult.Success = "true"; retResult.Content = document.OuterXml; return retResult.ToString(); } else { retResult.Success = "false"; retResult.Content = dtWork.Errmsg.Trim(); return retResult.ToString(); } } catch (Exception ex) { retResult.Success = "false"; retResult.Content = ex.Message.Trim(); return retResult.ToString(); } }