/// <summary> /// 更新一条数据 /// </summary> public void UpdateStaffStoresInfo(StaffStoresInfo model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update tbStaffStoresInfo set "); strSql.Append("StaffID=@StaffID,"); strSql.Append("StoresID=@StoresID,"); strSql.Append("sType=@sType,"); strSql.Append("sDateTime=@sDateTime,"); strSql.Append("sAppendTime=@sAppendTime"); strSql.Append(" where StaffStoresID=@StaffStoresID "); SqlParameter[] parameters = { new SqlParameter("@StaffStoresID", SqlDbType.Int, 4), new SqlParameter("@StaffID", SqlDbType.Int, 4), new SqlParameter("@StoresID", SqlDbType.Int, 4), new SqlParameter("@sType", SqlDbType.Int, 4), new SqlParameter("@sDateTime", SqlDbType.DateTime), new SqlParameter("@sAppendTime", SqlDbType.DateTime) }; parameters[0].Value = model.StaffStoresID; parameters[1].Value = model.StaffID; parameters[2].Value = model.StoresID; parameters[3].Value = model.sType; parameters[4].Value = model.sDateTime; parameters[5].Value = model.sAppendTime; DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters); }
/// <summary> /// 增加一条数据 /// </summary> public int AddStaffStoresInfo(StaffStoresInfo model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into tbStaffStoresInfo("); strSql.Append("StaffID,StoresID,sType,sDateTime,sAppendTime)"); strSql.Append(" values ("); strSql.Append("@StaffID,@StoresID,@sType,@sDateTime,@sAppendTime)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@StaffID", SqlDbType.Int, 4), new SqlParameter("@StoresID", SqlDbType.Int, 4), new SqlParameter("@sType", SqlDbType.Int, 4), new SqlParameter("@sDateTime", SqlDbType.DateTime), new SqlParameter("@sAppendTime", SqlDbType.DateTime) }; parameters[0].Value = model.StaffID; parameters[1].Value = model.StoresID; parameters[2].Value = model.sType; parameters[3].Value = model.sDateTime; parameters[4].Value = model.sAppendTime; object obj = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters); if (obj == null) { return(1); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 得到一个对象实体 /// </summary> public StaffStoresInfo GetStaffStoresInfoModel(int StaffStoresID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 StaffStoresID,StaffID,StoresID,sType,sDateTime,sAppendTime,(select sName from tbStoresInfo where StoresID=tbStaffStoresInfo.StoresID) as StoresName,(select sName from tbStaffInfo where StaffID=tbStaffStoresInfo.StaffID) as StaffName from tbStaffStoresInfo "); strSql.Append(" where StaffStoresID=@StaffStoresID "); SqlParameter[] parameters = { new SqlParameter("@StaffStoresID", SqlDbType.Int, 4) }; parameters[0].Value = StaffStoresID; StaffStoresInfo model = new StaffStoresInfo(); DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["StaffStoresID"].ToString() != "") { model.StaffStoresID = int.Parse(ds.Tables[0].Rows[0]["StaffStoresID"].ToString()); } if (ds.Tables[0].Rows[0]["StaffID"].ToString() != "") { model.StaffID = int.Parse(ds.Tables[0].Rows[0]["StaffID"].ToString()); } if (ds.Tables[0].Rows[0]["StoresID"].ToString() != "") { model.StoresID = int.Parse(ds.Tables[0].Rows[0]["StoresID"].ToString()); } if (ds.Tables[0].Rows[0]["sType"].ToString() != "") { model.sType = int.Parse(ds.Tables[0].Rows[0]["sType"].ToString()); } if (ds.Tables[0].Rows[0]["sDateTime"].ToString() != "") { model.sDateTime = DateTime.Parse(ds.Tables[0].Rows[0]["sDateTime"].ToString()); } if (ds.Tables[0].Rows[0]["sAppendTime"].ToString() != "") { model.sAppendTime = DateTime.Parse(ds.Tables[0].Rows[0]["sAppendTime"].ToString()); } model.StaffName = ds.Tables[0].Rows[0]["StaffName"].ToString(); model.StoresName = ds.Tables[0].Rows[0]["StoresName"].ToString(); return(model); } else { return(null); } }
protected void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("4-2")) { if (ispost) { string PathStr = Utils.GetMapPath(config.DataPath.ToString()); string fileExtension = ""; string fileName = ""; string thispath = DateTime.Now.Year + "-" + DateTime.Now.Month; ArrayList filearr = new ArrayList(); int importdata_count = 0; int importdata_count_Stores = 0; int importdata_count_Staff = 0; int importdata_count_StaffB = 0; int importdata_count_All = 0; if (!Directory.Exists(PathStr + thispath)) { Directory.CreateDirectory(PathStr + thispath); } //文件上传 HttpFileCollection files = HttpContext.Current.Request.Files; try { if (files.Count > 0) { for (int i = 0; i < files.Count; i++) { HttpPostedFile postedFile = files[i]; fileName = System.IO.Path.GetFileName(postedFile.FileName); if (Utils.ChkSQL(fileName) != "") { fileExtension = System.IO.Path.GetExtension(fileName).ToLower(); if (fileExtension == ".xls") { postedFile.SaveAs(PathStr + thispath + "/" + fileName); filearr.Add(PathStr + thispath + "/" + fileName); } } } } if (filearr.Count > 0) { StoresInfo si = new StoresInfo(); StaffInfo sfi = new StaffInfo(); StaffInfo sfiB = new StaffInfo(); StaffStoresInfo ssi = new StaffStoresInfo(); YHsysInfo yhi = new YHsysInfo(); RegionInfo ri = new RegionInfo(); string Stores_sCode = ""; string Stores_sName = ""; string Stores_sType = ""; int Stores_YHsysID = 0; int Stores_RegionID = 0; string Staff_Name = ""; string Staff_NameB = ""; DateTime Staff_sDateTime = DateTime.Parse("2009-1-1");//默认值 try { for (int j = 0; j < filearr.Count; j++) { try { DataSet ds = Excels.ExcelToDataTable(filearr[j].ToString()); DataTable dt = new DataTable(); try { dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString() != "" && dr[1].ToString() != "")//客户名称 { //客户、门店 Stores_sCode = dr[0].ToString().Trim(); Stores_sName = dr[1].ToString().Trim(); Stores_sType = dr[2].ToString().Trim(); Stores_YHsysID = 0; Stores_RegionID = 0; if (dr[3].ToString().Trim() != "") { yhi = tbYHsysInfo.GetYHsysInfoModelByName(dr[3].ToString().Trim()); if (yhi != null) { Stores_YHsysID = yhi.YHsysID; } } if (dr[5].ToString().Trim() != "") { ri = tbRegionInfo.GetRegionInfoModelLikeName(dr[5].ToString().Trim()); if (ri != null) { Stores_RegionID = ri.RegionID; } } si = tbStoresInfo.GetStoresInfoModelByName(Stores_sName); if (si == null) { si = new StoresInfo(); si.sName = Stores_sName; si.sCode = Stores_sCode; si.sType = Stores_sType; si.YHsysID = Stores_YHsysID; si.RegionID = Stores_RegionID; si.sState = 0; si.sAppendTime = DateTime.Now; si.StoresID = tbStoresInfo.AddStoresInfo(si); importdata_count_Stores++; } else { si.sName = Stores_sName; si.sCode = Stores_sCode; si.sType = Stores_sType; si.YHsysID = Stores_YHsysID; si.RegionID = Stores_RegionID; si.sState = 0; try { tbStoresInfo.UpdateStoresInfo(si); importdata_count_Stores++; } catch { } } //业务员 Staff_Name = dr[6].ToString().Trim(); if (Staff_Name.Trim() != "") { sfi = tbStaffInfo.GetStaffInfoModelByName(Staff_Name.Trim()); if (sfi == null) { sfi = new StaffInfo(); sfi.sName = Staff_Name.Trim(); sfi.sSex = ""; sfi.sType = 1; sfi.sTel = ""; sfi.sState = 0; sfi.sAppendTime = DateTime.Now; sfi.StaffID = tbStaffInfo.AddStaffInfo(sfi); importdata_count_Staff++; } } else { sfi = new StaffInfo(); sfi.StaffID = 0; } //促销员 Staff_NameB = dr[7].ToString().Trim(); if (Staff_NameB.Trim() != "") { sfiB = tbStaffInfo.GetStaffInfoModelByName(Staff_NameB.Trim()); if (sfiB == null) { sfiB = new StaffInfo(); sfiB.sName = Staff_NameB.Trim(); sfiB.sSex = "女"; sfiB.sType = 2; sfiB.sTel = dr[8].ToString().Trim(); sfiB.sState = 0; sfiB.sAppendTime = DateTime.Now; sfiB.StaffID = tbStaffInfo.AddStaffInfo(sfiB); importdata_count_StaffB++; } else { sfiB.sTel = dr[8].ToString().Trim(); tbStaffInfo.UpdateStaffInfo(sfiB); } } else { sfiB = new StaffInfo(); sfiB.StaffID = 0; } if (dr[9].ToString().Trim() != "") { Staff_sDateTime = DateTime.Parse(dr[9].ToString().Trim()); } else { Staff_sDateTime = DateTime.Parse("2009-1-1"); } //人员门店绑定 if (si.StoresID > 0) { ssi.StoresID = si.StoresID; if (sfi.StaffID > 0)//绑定业务员 { ssi.StaffID = sfi.StaffID; ssi.sType = 0; ssi.sDateTime = DateTime.Parse("2009-1-1"); ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); importdata_count++; } if (sfiB.StaffID > 0)//绑定促销员 { ssi.StaffID = sfiB.StaffID; ssi.sType = 0; ssi.sDateTime = Staff_sDateTime; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); importdata_count++; } } importdata_count_All++; } } } finally { ds.Clear(); ds.Dispose(); } } catch (Exception ex) { AddErrLine("<b>文件格式错误,请将 Xls 文件用 Excel 另存后再导入!</b>:<br>" + ex); //AddScript("window.setTimeout('history.back(1);',5000);"); } } if (importdata_count > 0) { AddMsgLine("数据导入成功!共导入数据[" + importdata_count_All.ToString() + "]条,门店[" + importdata_count_Stores.ToString() + "]条,业务员[" + importdata_count_Staff.ToString() + "]条,促销员[" + importdata_count_StaffB.ToString() + "]条,绑定上岗记录[" + importdata_count.ToString() + "]条."); AddScript("window.setTimeout('window.parent.HidBox();',5000);"); } else { AddErrLine("系统忙!导入失败!"); //AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { si = null; sfi = null; sfiB = null; ssi = null; yhi = null; ri = null; } } else { AddErrLine("为发现任何数据!导入失败!"); AddScript("window.setTimeout('history.back(1);',1000);"); } } finally { files = null; filearr.Clear(); } } } else { AddErrLine("权限不足!"); AddScript("window.parent.HidBox();"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
protected virtual void Page_Load(object sender, EventArgs e) { Act = HTTPRequest.GetString("Act"); StaffID = HTTPRequest.GetInt("sid", 0); if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("4-2-1") || CheckUserPopedoms("4-2-2")) { if (StaffID > 0) { si = tbStaffInfo.GetStaffInfoModel(StaffID); if (ispost) { tSidStr = Utils.ChkSQL(HTTPRequest.GetString("tSidStr")); aDate = DateTime.Parse(HTTPRequest.GetString("aDate")); StaffStoresInfo ssi = new StaffStoresInfo(); try { string[] tSidStrArray = Utils.SplitString(tSidStr, ","); if (Act == "Up") { if (si.sType == 1 || si.sType == 2) { if (tSidStr.Trim() != "") { foreach (string sid in tSidStrArray) { if (sid.Trim() != "") { ssi.StoresID = Utils.StrToInt(sid.Trim(), 0); ssi.StaffID = StaffID; ssi.sType = 0; ssi.sDateTime = aDate; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); } } } else { AddErrLine("请选择门店客户!"); AddScript("window.setTimeout('history.back(1);',2000);"); } } else { ssi.StoresID = -1; ssi.StaffID = StaffID; ssi.sType = 0; ssi.sDateTime = aDate; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); } } if (Act == "Down") { if (si.sType == 1 || si.sType == 2) { if (tSidStr.Trim() != "") { foreach (string sid in tSidStrArray) { if (sid.Trim() != "") { ssi.StoresID = Utils.StrToInt(sid.Trim(), 0); ssi.StaffID = StaffID; ssi.sType = 1; ssi.sDateTime = aDate; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); } } } else { AddErrLine("请选择门店客户!"); AddScript("window.setTimeout('history.back(1);',2000);"); } } else { ssi.StoresID = -1; ssi.StaffID = StaffID; ssi.sType = 1; ssi.sDateTime = aDate; ssi.sAppendTime = DateTime.Now; tbStaffStoresInfo.AddStaffStoresInfo(ssi); } } AddMsgLine("操作成功!"); AddScript("window.setTimeout('window.parent.HidBox();',2000);"); } finally { ssi = null; } } else { if (Act == "Up") { sType = si.sType; //需过滤当前已有人员在岗记录 dList = tbStoresInfo.GetStoresInfoList(" sState=0 and " + " StoresID not in( " + " select ss.StoresID from tbStaffStoresInfo ss " + " where ss.sType=0 and ss.StaffID in(select StaffID from tbStaffInfo where sType =" + sType + ") " + " and ss.StaffID not in(select StaffID from tbStaffStoresInfo where StoresID=ss.StoresID and StaffID in(select StaffID from tbStaffInfo where sType =" + sType + ") and sType=1 and sDateTime<GETDATE()) " + " ) " + " order by CustomersClassID,StoresID desc").Tables[0]; } if (Act == "Down") { dList = tbStaffStoresInfo.GetStaff_StoresList(StaffID, DateTime.Now.AddYears(-100), DateTime.Now, -1); DataView view = new DataView(); view.Table = dList; view.RowFilter = "edate > '" + DateTime.Now + "'";//离岗时间大于当前的 dList = view.ToTable(); } } } else { AddErrLine("参数错误,请重试!"); AddScript("window.setTimeout('history.back(1);',2000);"); } } else { AddErrLine("权限不足!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }
/// <summary> /// 更新一条数据 /// </summary> public static void UpdateStaffStoresInfo(StaffStoresInfo model) { DatabaseProvider.GetInstance().UpdateStaffStoresInfo(model); }
/// <summary> /// 增加一条数据 /// </summary> public static int AddStaffStoresInfo(StaffStoresInfo model) { return(DatabaseProvider.GetInstance().AddStaffStoresInfo(model)); }
protected virtual void Page_Load(object sender, EventArgs e) { if (this.userid > 0) { if (CheckUserPopedoms("X") || CheckUserPopedoms("4-2")) { Act = HTTPRequest.GetString("Act"); if (Act == "Edit") { StaffStoresID = Utils.StrToInt(HTTPRequest.GetString("sid"), 0); si = tbStaffStoresInfo.GetStaffStoresInfoModel(StaffStoresID); } if (ispost) { StaffID = Utils.StrToInt(HTTPRequest.GetString("StaffID"), 0); StoresID = Utils.StrToInt(HTTPRequest.GetString("StoresID"), 0); sType = Utils.StrToInt(HTTPRequest.GetString("sType"), 0); sDateTime = Utils.IsDateString(HTTPRequest.GetString("sDateTime"))?DateTime.Parse(HTTPRequest.GetString("sDateTime")):DateTime.Now; if (StaffID > 0 && StoresID > 0) { si.StoresID = StoresID; si.StaffID = StaffID; si.sDateTime = sDateTime; si.sType = sType; if (Act == "Add") { si.sAppendTime = DateTime.Now; if (tbStaffStoresInfo.AddStaffStoresInfo(si) > 0) { AddMsgLine("创建成功!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } else { AddErrLine("创建失败!"); AddScript("window.setTimeout('history.back(1);',1000);"); } } if (Act == "Edit") { try { tbStaffStoresInfo.UpdateStaffStoresInfo(si); AddMsgLine("修改成功!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } catch (Exception ex) { AddErrLine("修改失败!<br/>" + ex); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } } else { AddErrLine("参数错误!<br/>"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } else { if (Act == "Add") { si.StaffName = ""; si.StaffID = 0; si.StoresName = ""; si.StoresID = 0; } if (Act == "Del") { try { tbStaffStoresInfo.DeleteStaffStoresInfo(HTTPRequest.GetString("sid")); AddMsgLine("删除成功!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } catch (Exception ex) { AddErrLine("删除失败!<br/>" + ex); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } } } else { AddErrLine("权限不足!"); AddScript("window.setTimeout('window.parent.HidBox();',1000);"); } } else { AddErrLine("请先登录!"); SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer())); } }