//获取业绩，向临时表增添数据
public void GetYeJi(XHD.Model.crm_set_pripub_detail model, string strFactory, string strStart, string strEnd)
{
//获取有效转移客户，部门 hr_department ;临时写死，客户部ID=1；
//string strDept = "emp.factory_id='1000' and emp.isdelete=0 and ( emp.d_id in ( " + GetDeptNextAll().TrimEnd(',') + ")";
StringBuilder strSql = new StringBuilder();
strSql.Append(" Insert Into crm_order_feat (factory_Id,emp_code,order_feat,sales_return,realmoney,customer_Id,customer_Id_shop,customer_name_shop,customer_name,create_date,create_id)");
strSql.Append(" (select distinct '" + strFactory + "',c.BeSeats_id,yj.salemoney,yj.backmoney,yj.realmoney,l.C_customerid,yj.user_id,yj.user_name,C.customer,'" + strEnd + "','-1' ");
strSql.Append("from crm_customer as c inner join crm_contact as l on c.id=l.C_Customerid and c.isDelete=0 and privatecustomer_Id=52 ");
strSql.Append("inner join(");
strSql.Append("SELECT abcd.*,u.user_name,u.u_company FROM (");
strSql.Append("SELECT user_id,sum(salemoney) salemoney,sum(backmoney) backmoney,sum(salemoney+backmoney) AS realmoney,admin_user_name,name,d_id,dname ");
strSql.Append(" FROM hr_employee AS emp LEFT JOIN ");
strSql.Append(" (select admin_user_name,user_id,goods_amount as salemoney , 0 as backmoney from ecs_order_info where order_status !=9 and erp_order_sn<>'' and erp_time between UNIX_TIMESTAMP('");
strSql.Append(strStart + "') and UNIX_TIMESTAMP('" + strEnd + "')");
strSql.Append(" union all ");
strSql.Append("select xm as admin_user_name,dwmc as user_id, 0 as salemoney,d.je as backmoney from djkck as d inner join xlkck as x on x.djhm=d.djhm where d.rq between '" + strStart.Trim() + "' and '" + strEnd.Trim() + "' and d.je < 0 and date(d.RQ)<'2016-8-24 00:00:00'");
strSql.Append("union all ");
strSql.Append("select admin_user_name,user_id, 0 as salemoney, cast(ret_money_total as decimal(18,2)) as backmoney from ecs_return_order as e where e.erp_time between UNIX_TIMESTAMP('" + strStart + "') and UNIX_TIMESTAMP('" + strEnd + "') and e.ret_money_total<0 and e.erp_time>=1471968000 ");
strSql.Append(")AS abc ON emp.emp_code=abc.admin_user_name ");
strSql.Append("WHERE emp.factory_id='1000' and emp.isdelete=0 and ( emp.d_id in (" + GetDeptNextAll().TrimEnd(',') + ")) and admin_user_name is not null ");
strSql.Append("GROUP BY user_id ORDER BY convert(user_id,signed) ");
strSql.Append(")AS abcd ");
strSql.Append("LEFT JOIN ecs_users AS u ON abcd.user_id=u.user_id ");
strSql.Append(") as yj ");
strSql.Append("on l.C_tel=yj.user_name ");
strSql.Append(")");
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 向临时表增添数据
/// </summary>
public bool Add(XHD.Model.crm_set_pripub_detail model)
{
// ConvertIntDateTime
StringBuilder strSql = new StringBuilder();
strSql.Append(" Insert Into crm_order_feat (factory_Id,emp_code,order_feat,customer_Id,customer_Id_shop,customer_name_shop,customer_name,create_date,create_id)");
strSql.Append(" select '" + model.factory_Id + "',abc.emp_code, abc.tall,c.id,abc.user_id,");
strSql.Append(" abc.user_name,c.customer,'" + model.C_createDate + "','" + model.C_createId + "' from ");
strSql.Append(" crm_customer as c inner join crm_contact as l on c.id=l.C_Customerid");
strSql.Append(" inner join ");
strSql.Append(" (select e.emp_code,e.name,e.dname,e.post,o.order_status,");
strSql.Append(" o.user_id,u.user_name,sum( o.goods_amount+o.shipping_fee) as tall");
strSql.Append(" from hr_employee as e inner join ecs_order_info as o on e.emp_code=o.admin_user_name and o.order_status =1");
strSql.Append(" and o.confirm_time between " + model.date_start + " and " + model.date_end);
strSql.Append(" left join ecs_users as u on o.user_id=u.user_id ");
strSql.Append(" group by e.emp_code,e.name,e.dname,e.post,o.user_id,u.user_name");
strSql.Append(" ) as abc on l.C_tel=abc.user_name ");
strSql.Append(" where c.factory_id='" + model.factory_Id + "'");
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(XHD.Model.crm_set_pripub_detail model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update crm_set_pripub_detail set ");
strSql.Append("factory_Id=@factory_Id,");
strSql.Append("current_year=@current_year,");
strSql.Append("pripub_type=@pripub_type,");
strSql.Append("date_start=@date_start,");
strSql.Append("date_end=@date_end,");
strSql.Append("feat=@feat,");
strSql.Append("C_createId=@C_createId,");
strSql.Append("C_createName=@C_createName,");
strSql.Append("C_createDate=@C_createDate,");
strSql.Append("move_Qty=@move_Qty");
strSql.Append(" where Id=@Id ");
MySqlParameter[] parameters =
{
new MySqlParameter("@factory_Id", MySqlDbType.VarChar, 60),
new MySqlParameter("@current_year", MySqlDbType.Int32, 4),
new MySqlParameter("@pripub_type", MySqlDbType.VarChar, 20),
new MySqlParameter("@date_start", MySqlDbType.Int32, 11),
new MySqlParameter("@date_end", MySqlDbType.Int32, 11),
new MySqlParameter("@feat", MySqlDbType.Float, 20),
new MySqlParameter("@C_createId", MySqlDbType.Int32, 11),
new MySqlParameter("@C_createName", MySqlDbType.VarChar, 60),
new MySqlParameter("@C_createDate", MySqlDbType.DateTime),
new MySqlParameter("@move_Qty", MySqlDbType.Int32, 11),
new MySqlParameter("@Id", MySqlDbType.Int32, 11)
};
parameters[0].Value = model.factory_Id;
parameters[1].Value = model.current_year;
parameters[2].Value = model.pripub_type;
parameters[3].Value = model.date_start;
parameters[4].Value = model.date_end;
parameters[5].Value = model.feat;
parameters[6].Value = model.C_createId;
parameters[7].Value = model.C_createName;
parameters[8].Value = model.C_createDate;
parameters[9].Value = model.move_Qty;
parameters[10].Value = model.Id;
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public XHD.Model.crm_set_pripub_detail DataRowToModel(DataRow row)
{
XHD.Model.crm_set_pripub_detail model = new XHD.Model.crm_set_pripub_detail();
if (row != null)
{
if (row["Id"] != null && row["Id"].ToString() != "")
{
model.Id = int.Parse(row["Id"].ToString());
}
if (row["factory_Id"] != null)
{
model.factory_Id = row["factory_Id"].ToString();
}
if (row["current_year"] != null && row["current_year"].ToString() != "")
{
model.current_year = int.Parse(row["current_year"].ToString());
}
if (row["pripub_type"] != null)
{
model.pripub_type = row["pripub_type"].ToString();
}
if (row["date_start"] != null && row["date_start"].ToString() != "")
{
model.date_start = int.Parse(row["date_start"].ToString());
}
if (row["date_end"] != null && row["date_end"].ToString() != "")
{
model.date_end = int.Parse(row["date_end"].ToString());
}
if (row["feat"] != null && row["feat"].ToString() != "")
{
model.feat = decimal.Parse(row["feat"].ToString());
}
if (row["C_createId"] != null && row["C_createId"].ToString() != "")
{
model.C_createId = int.Parse(row["C_createId"].ToString());
}
if (row["C_createName"] != null)
{
model.C_createName = row["C_createName"].ToString();
}
if (row["C_createDate"] != null && row["C_createDate"].ToString() != "")
{
model.C_createDate = DateTime.Parse(row["C_createDate"].ToString());
}
if (row["move_Qty"] != null && row["move_Qty"].ToString() != "")
{
model.move_Qty = int.Parse(row["move_Qty"].ToString());
}
}
return(model);
}
/// <summary>
/// 向临时表增添数据
/// </summary>
public int AddNew(XHD.Model.crm_set_pripub_detail model)
{
DateTime startdata = XHD.Common.TimeParser.ConvertIntDateTime(Convert.ToDouble(model.date_start));
DateTime enddata = XHD.Common.TimeParser.ConvertIntDateTime(Convert.ToDouble(model.date_end));
StringBuilder strSql = new StringBuilder();
strSql.Append(" Insert Into crm_order_feat (factory_Id,emp_code,order_feat,sales_return,customer_Id,customer_Id_shop,customer_name_shop,customer_name,create_date,create_id)");
//strSql.Append(" Insert Into crm_order_feat (factory_Id,emp_code,order_feat,sales_return,customer_Id,customer_Id_shop,customer_name_shop,customer_name,create_date,create_id)");
//strSql.Append(" select '" + model.factory_Id + "',abc.emp_code, abc.tall,abc.returnNum,c.id,abc.user_id,");
//strSql.Append(" abc.user_name,c.customer,'" + model.C_createDate + "','" + model.C_createId + "' from ");
//strSql.Append(" crm_customer as c inner join crm_contact as l on c.id=l.C_Customerid");
//strSql.Append(" inner join ");
//strSql.Append(" (select e.emp_code,e.name,e.dname,e.post,o.order_status,");
//strSql.Append(" o.user_id,u.user_name,sum( o.goods_amount+o.shipping_fee) as tall,sum(xd.JE) as returnNum");
//strSql.Append(" from hr_employee as e inner join ecs_order_info as o on e.emp_code=o.admin_user_name and e.factory_id='" + model.factory_Id + "' and o.order_status !=9");
//strSql.Append(" and o.erp_time between " + model.date_start + " and " + model.date_end);
//strSql.Append(" left join (select distinct x.* from XLKCK as x where date(x.RQ) >='" + startdata + "' and date(x.RQ)<='" + enddata + "' and x.JE<0 and (x.BZ !='换' and x.BZ !='换货' or x.BZ is null) ) as xd on o.user_id= xd.DWMC ");
//strSql.Append(" left join ecs_users as u on o.user_id=u.user_id ");
//strSql.Append(" group by e.emp_code,e.name,e.dname,e.post,o.user_id,u.user_name");
//strSql.Append(" ) as abc on l.C_tel=abc.user_name ");
//strSql.Append(" where c.factory_id='" + model.factory_Id + "' and c.isdelete=0");
strSql.Append(" select '" + model.factory_Id + "', abc.admin_user_name,abc.tall,abc.returnNum,c.id,abc.user_id,abc.user_name,c.customer,'"+ model.C_createDate + "','" + model.C_createId + "' ");
strSql.Append(" from crm_customer as c inner join crm_contact as l on c.id=l.C_Customerid inner join ( ");
strSql.Append(" select u.user_name,u.admin_user_name,u.user_id,sum( o.goods_amount+o.shipping_fee) as tall ,sum(xd.JE) as returnNum ");
strSql.Append(" from ecs_users as u left join ecs_order_info as o on o.user_id=u.user_id ");
strSql.Append(" left join (select distinct x.* from XLKCK as x where date(x.RQ) >='" + startdata + "' and date(x.RQ)<='" + enddata + "' and x.JE<0 and (x.BZ !='换' and x.BZ !='换货' or x.BZ is null) ) as xd on u.user_id= xd.DWMC ");
strSql.Append(" where o.order_status !=9 and o.erp_time between " + model.date_start + " and " + model.date_end);
strSql.Append(" group by u.user_name,u.admin_user_name,u.user_id ");
strSql.Append(" ) as abc on l.C_tel=abc.user_name ");
strSql.Append(" where c.factory_id='" + model.factory_Id + "' and c.isdelete=0 ");
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(rows);
}
else
{
return(0);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(XHD.Model.crm_set_pripub_detail model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into crm_set_pripub_detail(");
strSql.Append("Id,factory_Id,current_year,pripub_type,date_start,date_end,feat,C_createId,C_createName,C_createDate,move_Qty)");
strSql.Append(" values (");
strSql.Append("@Id,@factory_Id,@current_year,@pripub_type,@date_start,@date_end,@feat,@C_createId,@C_createName,@C_createDate,@move_Qty)");
MySqlParameter[] parameters =
{
new MySqlParameter("@Id", MySqlDbType.Int32, 11),
new MySqlParameter("@factory_Id", MySqlDbType.VarChar, 60),
new MySqlParameter("@current_year", MySqlDbType.Int32, 4),
new MySqlParameter("@pripub_type", MySqlDbType.VarChar, 20),
new MySqlParameter("@date_start", MySqlDbType.Int32, 11),
new MySqlParameter("@date_end", MySqlDbType.Int32, 11),
new MySqlParameter("@feat", MySqlDbType.Float, 20),
new MySqlParameter("@C_createId", MySqlDbType.Int32, 11),
new MySqlParameter("@C_createName", MySqlDbType.VarChar, 60),
new MySqlParameter("@C_createDate", MySqlDbType.DateTime),
new MySqlParameter("@move_Qty", MySqlDbType.Int32, 11)
};
parameters[0].Value = model.Id;
parameters[1].Value = model.factory_Id;
parameters[2].Value = model.current_year;
parameters[3].Value = model.pripub_type;
parameters[4].Value = model.date_start;
parameters[5].Value = model.date_end;
parameters[6].Value = model.feat;
parameters[7].Value = model.C_createId;
parameters[8].Value = model.C_createName;
parameters[9].Value = model.C_createDate;
parameters[10].Value = model.move_Qty;
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public XHD.Model.crm_set_pripub_detail GetModel(int Id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select Id,factory_Id,current_year,pripub_type,date_start,date_end,feat,C_createId,C_createName,C_createDate,move_Qty from crm_set_pripub_detail ");
strSql.Append(" where Id=@Id ");
MySqlParameter[] parameters =
{
new MySqlParameter("@Id", MySqlDbType.Int32, 11)
};
parameters[0].Value = Id;
XHD.Model.crm_set_pripub_detail model = new XHD.Model.crm_set_pripub_detail();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}