//获取业绩,向临时表增添数据 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); } }