/// <summary>
/// 增加一条数据
/// </summary>
public int AddPaymentSystem(PaymentSystemInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tbPaymentSystemInfo(");
strSql.Append("pName,pAppendTime)");
strSql.Append(" values (");
strSql.Append("@pName,@pAppendTime)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@pName", SqlDbType.VarChar, 50),
new SqlParameter("@pAppendTime", SqlDbType.DateTime)
};
parameters[0].Value = model.pName;
parameters[1].Value = model.pAppendTime;
object obj = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
if (obj == null)
{
return(1);
}
else
{
return(Convert.ToInt32(obj));
}
}
public PaymentSystemInfo GetPaymentSystemModel(string PaymentSystemName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 PaymentSystemID,pName,pAppendTime from tbPaymentSystemInfo ");
strSql.Append(" where pName=@PaymentSystemName ");
SqlParameter[] parameters =
{
new SqlParameter("@PaymentSystemName", SqlDbType.VarChar, 50)
};
parameters[0].Value = PaymentSystemName;
PaymentSystemInfo model = new PaymentSystemInfo();
DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["PaymentSystemID"].ToString() != "")
{
model.PaymentSystemID = int.Parse(ds.Tables[0].Rows[0]["PaymentSystemID"].ToString());
}
model.pName = ds.Tables[0].Rows[0]["pName"].ToString();
if (ds.Tables[0].Rows[0]["pAppendTime"].ToString() != "")
{
model.pAppendTime = DateTime.Parse(ds.Tables[0].Rows[0]["pAppendTime"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void UpdatePaymentSystem(PaymentSystemInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tbPaymentSystemInfo set ");
strSql.Append("pName=@pName,");
strSql.Append("pAppendTime=@pAppendTime");
strSql.Append(" where PaymentSystemID=@PaymentSystemID ");
SqlParameter[] parameters =
{
new SqlParameter("@PaymentSystemID", SqlDbType.Int, 4),
new SqlParameter("@pName", SqlDbType.VarChar, 50),
new SqlParameter("@pAppendTime", SqlDbType.DateTime)
};
parameters[0].Value = model.PaymentSystemID;
parameters[1].Value = model.pName;
parameters[2].Value = model.pAppendTime;
DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
}
/// <summary>
/// 更新一条数据
/// </summary>
public static void UpdatePaymentSystem(PaymentSystemInfo model)
{
DatabaseProvider.GetInstance().UpdatePaymentSystem(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public static int AddPaymentSystem(PaymentSystemInfo model)
{
return(DatabaseProvider.GetInstance().AddPaymentSystem(model));
}
protected virtual void Page_Load(object sender, EventArgs e)
{
if (this.userid > 0)
{
if (CheckUserPopedoms("X") || CheckUserPopedoms("2-2-6"))
{
Act = HTTPRequest.GetString("Act");
if (Act == "Edit")
{
PaymentSystemID = Utils.StrToInt(HTTPRequest.GetString("pid"), 0);
pi = tbPaymentSystemInfo.GetPaymentSystemModel(PaymentSystemID);
}
if (ispost)
{
pName = Utils.ChkSQL(HTTPRequest.GetString("pName"));
if (pName.Trim() != "")
{
pi.PaymentSystemID = PaymentSystemID;
pi.pName = pName;
if (Act == "Add")
{
pi.pAppendTime = DateTime.Now;
if (tbPaymentSystemInfo.AddPaymentSystem(pi) > 0)
{
Logs.AddEventLog(this.userid, "新增结算系统." + pi.pName);
AddMsgLine("创建成功!");
AddScript("window.setTimeout('window.parent.HidBox();',2000);");
}
else
{
AddErrLine("创建失败!");
AddScript("history.back(1);");
}
}
if (Act == "Edit")
{
try
{
tbPaymentSystemInfo.UpdatePaymentSystem(pi);
Logs.AddEventLog(this.userid, "修改结算系统." + pi.pName);
AddMsgLine("修改成功!");
AddScript("window.setTimeout('window.parent.HidBox();',2000);");
}
catch (Exception ex)
{
AddErrLine("修改失败!<br/>" + ex);
AddScript("window.setTimeout('window.parent.HidBox();',2000);");
}
}
}
else
{
AddErrLine("参数错误!<br/>");
AddScript("window.setTimeout('window.parent.HidBox();',2000);");
}
}
else
{
if (Act == "Add")
{
}
if (Act == "Del")
{
try
{
tbPaymentSystemInfo.DeletePaymentSystem(HTTPRequest.GetString("pid"));
Logs.AddEventLog(this.userid, "删除结算系统." + HTTPRequest.GetString("pid"));
AddMsgLine("删除成功!");
AddScript("window.setTimeout('window.parent.HidBox();',2000);");
}
catch (Exception ex)
{
AddErrLine("创建失败!<br/>" + ex);
AddScript("window.setTimeout('window.parent.HidBox();',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()));
}
}
protected virtual void Page_Load(object sender, EventArgs e)
{
Act = HTTPRequest.GetString("Act");
if (this.userid > 0)
{
if (CheckUserPopedoms("X") || CheckUserPopedoms("7-2-1-6-1"))
{
if (CheckUserPopedoms("X") || CheckUserPopedoms("7-2-1-6-2"))
{
ShowPrice = true;
}
OrderNumber = Utils.ChkSQL(HTTPRequest.GetString("OrderNumber")).Trim();
StoresID = HTTPRequest.GetInt("StoresID", 0);
StoresName = Utils.ChkSQL(HTTPRequest.GetString("StoresName"));
PaymentSystemID = HTTPRequest.GetInt("PaymentSystemID", 0);
PaymentSystemName = Utils.ChkSQL(HTTPRequest.GetString("PaymentSystemName")).Trim();
SingleMemberID = HTTPRequest.GetInt("SingleMemberID", 0);
SingleMemberName = Utils.ChkSQL(HTTPRequest.GetString("SingleMemberName")).Trim();
if (OrderNumber != "")
{
OrderNumber = "," + OrderNumber + ",";
OrderNumber = Utils.ReSQLSetTxt(OrderNumber);
}
if (PaymentSystemID == 0)
{
PaymentSystemInfo psi = tbPaymentSystemInfo.GetPaymentSystemModel(PaymentSystemName);
if (psi != null)
{
PaymentSystemID = psi.PaymentSystemID;
}
}
sDate = (HTTPRequest.GetString("sDate").Trim() != "") ? Convert.ToDateTime(HTTPRequest.GetString("sDate").Trim()) : DateTime.Now;
bDate = (HTTPRequest.GetString("bDate").Trim() != "") ? Convert.ToDateTime(HTTPRequest.GetString("bDate").Trim()) :Convert.ToDateTime(DateTime.Now.Year + "-" + DateTime.Now.Month + "-1");
eDate = (HTTPRequest.GetString("eDate").Trim() != "") ? Convert.ToDateTime(HTTPRequest.GetString("eDate").Trim()) : DateTime.Now;
ReDateType = HTTPRequest.GetInt("ReDateType", 1); //统计类型
ReType = HTTPRequest.GetInt("ReType", 1); //默认商品销售
DateType = HTTPRequest.GetInt("DateType", 0); //默认列表统计
NOJoinSales = HTTPRequest.GetInt("NOJoinSales", 0);
dType = HTTPRequest.GetInt("dType", 0);
oSteps = HTTPRequest.GetInt("oSteps", 5);
CostPrice = HTTPRequest.GetInt("CostPrice", 1);//默认不计算成本
OrderStpes = Orders.GetOrderSteps();
if (CostPrice == 1)
{
ShowPrice = false;
}
sSysList = tbPaymentSystemInfo.GetPaymentSystemList(" 1=1 order by PaymentSystemID desc").Tables[0];
string tSQL = "";
if (ispost)
{
//AddErrLine ("ReType:"+ReType+", bDate:"+bDate+", eDate:"+eDate+", NOJoinSales:"+NOJoinSales+", StoresID:"+StoresID+", PaymentSystemID:"+PaymentSystemID+", oSteps:"+oSteps+", dType:"+dType+", CostPrice:"+CostPrice+",SingleMemberID:"+SingleMemberID+",OrderNumber:"+OrderNumber);
dList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
//客户方式,销售，发出
if (ReType == 2 || ReType == 9)
{
tSQL = " 0=0 ";
if (StoresID > 0)
{
tSQL += " and StoresID=" + StoresID;
}
cList = tbStoresInfo.GetStoresInfoList(" " + tSQL + " order by sAppendTime desc").Tables[0];
int pCount = 0;
cList.Columns.Add("pCount", typeof(System.Int32));
foreach (DataRow dr in cList.Rows)
{
pCount = 0;
foreach (DataRow ddr in dList.Rows)
{
if (dr["StoresID"].ToString() == ddr["StoresID"].ToString())
{
//if (Convert.ToDecimal(ddr["oQuantity"]) > 0 || Convert.ToDecimal(ddr["oQuantity_t"]) > 0 || Convert.ToDecimal(ddr["oQuantity_z"]) > 0 || Convert.ToDecimal(ddr["oQuantity_y"]) > 0)
{
pCount++;
}
}
}
dr["pCount"] = pCount > 0 ? pCount + 1 : 0;
}
}
//开单员(销售)=10，开单员(出货)=11
if (ReType == 10 || ReType == 11)
{
tSQL = " 0=0 ";
}
//结算系统方式
if (ReType == 3)
{
tSQL = " 1=1 ";
if (PaymentSystemID > 0)
{
tSQL += " and PaymentSystemID=" + PaymentSystemID;
}
cList = tbPaymentSystemInfo.GetPaymentSystemList(" " + tSQL + " order by pAppendTime desc").Tables[0];
int pCount = 0;
cList.Columns.Add("pCount", typeof(System.Int32));
foreach (DataRow dr in cList.Rows)
{
pCount = 0;
foreach (DataRow ddr in dList.Rows)
{
if (dr["PaymentSystemID"].ToString() == ddr["PaymentSystemID"].ToString())
{
//if (Convert.ToDecimal(ddr["oQuantity"]) > 0 || Convert.ToDecimal(ddr["oQuantity_t"]) > 0 || Convert.ToDecimal(ddr["oQuantity_z"]) > 0 || Convert.ToDecimal(ddr["oQuantity_y"]) > 0)
{
pCount++;
}
}
}
dr["pCount"] = pCount > 0 ? pCount + 1 : 0;
}
}
// else
// {
// cList = DataClass.GetProductClassInfoList("").Tables[0];
// }
//业务员,促销员方式
if (ReType == 4 || ReType == 5 || ReType == 7)
{
tSQL = " 0=0 ";
cList = tbStaffInfo.GetStaffInfoList(" " + tSQL + " order by sAppendTime desc").Tables[0];
int pCount = 0;
cList.Columns.Add("pCount", typeof(System.Int32));
foreach (DataRow dr in cList.Rows)
{
pCount = 0;
foreach (DataRow ddr in dList.Rows)
{
if (dr["StaffID"].ToString() == ddr["StaffID"].ToString())
{
//if (Convert.ToDecimal(ddr["oQuantity"]) > 0 || Convert.ToDecimal(ddr["oQuantity_t"]) > 0 || Convert.ToDecimal(ddr["oQuantity_z"]) > 0 || Convert.ToDecimal(ddr["oQuantity_y"]) > 0)
{
pCount++;
}
}
}
dr["pCount"] = pCount > 0 ? pCount + 1 : 0;
}
}
//地区统计
if (ReType == 6)
{
int rCount = 0;
int pCount = 0;
rList = tbRegionInfo.GetRegionInfoList(" 0=0 order by rUpID asc").Tables[0];
DataRow r_dr = rList.NewRow();
r_dr["RegionID"] = 0;
r_dr["rName"] = "未归类";
r_dr["rUpID"] = 0;
r_dr["rOrder"] = 0;
r_dr["rState"] = 0;
r_dr["rAppendTime"] = DateTime.Now;
rList.Rows.Add(r_dr);
rList.Columns.Add("pCount", typeof(System.Int32));
rList.AcceptChanges();
tSQL = " 0=0 ";
if (StoresID > 0)
{
tSQL += " and StoresID=" + StoresID;
}
cList = tbStoresInfo.GetStoresInfoList(" " + tSQL + " order by sAppendTime desc").Tables[0];
cList.Columns.Add("pCount", typeof(System.Int32));
foreach (DataRow dr in cList.Rows)
{
pCount = 0;
foreach (DataRow ddr in dList.Rows)
{
if (dr["StoresID"].ToString() == ddr["StoresID"].ToString())
{
pCount++;
}
}
dr["pCount"] = pCount > 0 ? pCount + 1 : 0;
}
foreach (DataRow _dr in rList.Rows)
{
rCount = 0;
foreach (DataRow _ddr in cList.Rows)
{
if (_dr["RegionID"].ToString() == _ddr["RegionID"].ToString())
{
rCount += Convert.ToInt32(_ddr["pCount"].ToString());
}
}
_dr["pCount"] = rCount > 0 ? rCount + 1 : 0;
}
}
}
else
{
if (Act.IndexOf("Export") > -1)
{
//商品方式，销售，发出
if (ReType == 1 || ReType == 8)
{
eList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
DataTable dt = eList.Copy();
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(4);
dt.Columns.RemoveAt(4);
dt.Columns.RemoveAt(4);
dt.Columns.RemoveAt(4);
dt.Columns.RemoveAt(4);
dt.Columns.RemoveAt(5);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(17);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables[0].Columns[0].ColumnName = "商品默认成本";
ds.Tables[0].Columns[1].ColumnName = "商品加权平均成本";
ds.Tables[0].Columns[2].ColumnName = "商品条码";
ds.Tables[0].Columns[3].ColumnName = "商品名称";
ds.Tables[0].Columns[4].ColumnName = "装件数";
ds.Tables[0].Columns[5].ColumnName = "销售发货数量";
ds.Tables[0].Columns[6].ColumnName = "销售发货金额";
ds.Tables[0].Columns[7].ColumnName = "销售退货数量";
ds.Tables[0].Columns[8].ColumnName = "销售退货金额";
ds.Tables[0].Columns[9].ColumnName = "赠品数量";
ds.Tables[0].Columns[10].ColumnName = "赠品金额";
ds.Tables[0].Columns[11].ColumnName = "样品数量";
ds.Tables[0].Columns[12].ColumnName = "样品金额";
ds.Tables[0].Columns[13].ColumnName = "坏货数量";
ds.Tables[0].Columns[14].ColumnName = "坏货金额";
ds.Tables[0].Columns[15].ColumnName = "销售赠品数量";
ds.Tables[0].Columns[16].ColumnName = "销售赠品金额";
CreateExcel(ds.Tables[0], "Sales_Data_" + bDate.ToShortDateString() + ".xls");
}
//客户方式，销售，发出
if (ReType == 2 || ReType == 9)
{
eList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
DataTable dt = eList.Copy();
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(0);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables[0].Columns[0].ColumnName = "客户名称";
ds.Tables[0].Columns[1].ColumnName = "商品加权平均成本";
ds.Tables[0].Columns[2].ColumnName = "商品默认成本";
ds.Tables[0].Columns[3].ColumnName = "商品条码";
ds.Tables[0].Columns[4].ColumnName = "商品名称";
ds.Tables[0].Columns[5].ColumnName = "装件数";
ds.Tables[0].Columns[6].ColumnName = "销售发货数量";
ds.Tables[0].Columns[7].ColumnName = "销售发货金额";
ds.Tables[0].Columns[8].ColumnName = "销售退货数量";
ds.Tables[0].Columns[9].ColumnName = "销售退货金额";
ds.Tables[0].Columns[10].ColumnName = "赠品数量";
ds.Tables[0].Columns[11].ColumnName = "赠品金额";
ds.Tables[0].Columns[12].ColumnName = "样品数量";
ds.Tables[0].Columns[13].ColumnName = "样品金额";
ds.Tables[0].Columns[14].ColumnName = "销售赠品数量";
ds.Tables[0].Columns[15].ColumnName = "销售赠品金额";
CreateExcel(ds.Tables[0], "Sales_Data_" + bDate.ToShortDateString() + ".xls");
}
//结算系统
if (ReType == 3)
{
eList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
DataTable dt = eList.Copy();
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(1);
dt.Columns.RemoveAt(11);
dt.Columns.RemoveAt(11);
dt.Columns.RemoveAt(11);
dt.Columns.RemoveAt(11);
dt.Columns["ProductName"].SetOrdinal(1);
dt.Columns["pBarcode"].SetOrdinal(2);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables[0].Columns[0].ColumnName = "结算系统名称";
ds.Tables[0].Columns[1].ColumnName = "商品名称";
ds.Tables[0].Columns[2].ColumnName = "商品条码";
ds.Tables[0].Columns[3].ColumnName = "销售数量";
ds.Tables[0].Columns[4].ColumnName = "销售金额";
ds.Tables[0].Columns[5].ColumnName = "退货数量";
ds.Tables[0].Columns[6].ColumnName = "退货金额";
ds.Tables[0].Columns[7].ColumnName = "赠品数量";
ds.Tables[0].Columns[8].ColumnName = "赠品金额";
ds.Tables[0].Columns[9].ColumnName = "样品数量";
ds.Tables[0].Columns[10].ColumnName = "样品金额";
CreateExcel(ds.Tables[0], "Sales_Data_" + bDate.ToShortDateString() + ".xls");
}
//业务员,促销员方式
if (ReType == 4 || ReType == 5 || ReType == 7)
{
eList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
DataTable dt = eList.Copy();
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(0);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables[0].Columns[0].ColumnName = "姓名";
ds.Tables[0].Columns[1].ColumnName = "商品加权平均成本";
ds.Tables[0].Columns[2].ColumnName = "商品默认成本";
ds.Tables[0].Columns[3].ColumnName = "商品条码";
ds.Tables[0].Columns[4].ColumnName = "商品名称";
ds.Tables[0].Columns[5].ColumnName = "装件数";
ds.Tables[0].Columns[6].ColumnName = "销售发货数量";
ds.Tables[0].Columns[7].ColumnName = "销售发货金额";
ds.Tables[0].Columns[8].ColumnName = "销售退货数量";
ds.Tables[0].Columns[9].ColumnName = "销售退货金额";
ds.Tables[0].Columns[10].ColumnName = "赠品数量";
ds.Tables[0].Columns[11].ColumnName = "赠品金额";
ds.Tables[0].Columns[12].ColumnName = "样品数量";
ds.Tables[0].Columns[13].ColumnName = "样品金额";
ds.Tables[0].Columns[14].ColumnName = "销售赠品数量";
ds.Tables[0].Columns[15].ColumnName = "销售赠品金额";
CreateExcel(ds.Tables[0], "Sales_Data_" + bDate.ToShortDateString() + ".xls");
}
//按地区
if (ReType == 6)
{
eList = Caches.GetSalesReport(ReDateType, ReType, bDate, eDate, NOJoinSales, StoresID, PaymentSystemID, oSteps, dType, CostPrice, SingleMemberID, OrderNumber);
DataTable dt = eList.Copy();
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(1);
dt.Columns.RemoveAt(2);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables[0].Columns[0].ColumnName = "客户名称";
ds.Tables[0].Columns[1].ColumnName = "地区";
ds.Tables[0].Columns[2].ColumnName = "商品加权平均成本";
ds.Tables[0].Columns[3].ColumnName = "商品默认成本";
ds.Tables[0].Columns[4].ColumnName = "商品条码";
ds.Tables[0].Columns[5].ColumnName = "商品名称";
ds.Tables[0].Columns[6].ColumnName = "装件数";
ds.Tables[0].Columns[7].ColumnName = "销售发货数量";
ds.Tables[0].Columns[8].ColumnName = "销售发货金额";
ds.Tables[0].Columns[9].ColumnName = "销售退货数量";
ds.Tables[0].Columns[10].ColumnName = "销售退货金额";
ds.Tables[0].Columns[11].ColumnName = "赠品数量";
ds.Tables[0].Columns[12].ColumnName = "赠品金额";
ds.Tables[0].Columns[13].ColumnName = "样品数量";
ds.Tables[0].Columns[14].ColumnName = "样品金额";
ds.Tables[0].Columns[15].ColumnName = "销售赠品数量";
ds.Tables[0].Columns[16].ColumnName = "销售赠品金额";
CreateExcel(ds.Tables[0], "Sales_Data_" + bDate.ToShortDateString() + ".xls");
}
}
}
}
else
{
AddErrLine("权限不足!");
}
}
else
{
AddErrLine("请先登录!");
SetBackLink("login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer()));
SetMetaRefresh(1, "login.aspx?referer=" + Utils.UrlEncode(Utils.GetUrlReferrer()));
}
}