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