public List <T> GetDataTResult <T>(LambdaQuery.Mapping.QueryInfo <T> queryInfo, out int outParame)
{
var data = ObjectConvert.DataReaderToSpecifiedList <T>(reader, queryInfo);
outParame = handler();
reader.Close();
reader.Dispose();
return(data);
}
public string getSMIDbyType(DataTable p_dt, string p_Branch, string p_Type)
{
string cmdTxt = @" select u.ACCOUNT";
cmdTxt += @" from TB_EB_USER u";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID =u.USER_GUID";
cmdTxt += @" inner join TB_EB_JOB_FUNC jb on jb.FUNC_ID = ef.FUNC_ID";
cmdTxt += @" where g.GROUP_ID in(";
cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
cmdTxt += @" and u.EXPIRE_DATE='9999-12-31 23:59:59.997'";
cmdTxt += @" and jb.FUNC_NAME in (" + p_Type + ")";
cmdTxt += @" group by u.ACCOUNT,CREATE_DATE,jb.FUNC_NAME";
cmdTxt += @" order by jb.FUNC_NAME";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr); //解析SQL內的資料(可以是DataTable、DataGrid...)
dr.Dispose(); //切斷與資料庫連線
List <DataRow> l_list = new List <DataRow>();
foreach (DataRow row in p_dt.Rows)
{
l_list.Add(row);
}
string l_str員編 = "";
for (int i = 0; i < l_list.Count; i++)
{
l_str員編 += "'" + l_list[i][0].ToString() + "',";
}
return(l_str員編);
}
public void DateQuery(string p_orderno, string SDATE, string EDATE, string p_unit, string p_empid, DataTable p_dt)
{
string cmdTxt = @"select TASK_ID,f_orderno,f_unit,f_empid,f_editday";
cmdTxt += @" from TB_TONER_ORDER ";
cmdTxt += @" where 1=1";
if (p_orderno != "")
{
cmdTxt += @" and f_orderno = @p_orderno";
this.m_db.AddParameter("@p_orderno", p_orderno);
}
if (SDATE != "" && EDATE != "")
{
cmdTxt += @" and f_editday between @SDATE and @EDATE";
this.m_db.AddParameter("@SDATE", SDATE);
this.m_db.AddParameter("@EDATE", EDATE);
}
if (p_unit != "")
{
cmdTxt += @" and f_unit = @p_unit";
this.m_db.AddParameter("@p_unit", p_unit);
}
if (p_empid != "")
{
cmdTxt += @" and f_empid = @p_empid";
this.m_db.AddParameter("@p_empid", p_empid);
}
cmdTxt += @" order by f_editday";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
}
public void MonthQuery(string SDATE, string EDATE, string p_unit, string p_empid, DataTable p_dt)
{
string cmdTxt = @"select o.f_unit,o.f_empid,o.f_editday,t.f_proname,sum(t.f_amount) as f_amount,sum(t.f_price*t.f_amount) as f_total";
cmdTxt += @" from TB_TONER_ORDER o";
cmdTxt += @" inner join TB_TONER_ORDERDETAIL t on t.f_orderno = o.f_orderno";
cmdTxt += @" where 1=1";
if (SDATE != "" && EDATE != "")
{
cmdTxt += @" and o.f_editday between @SDATE and @EDATE";
this.m_db.AddParameter("@SDATE", SDATE);
this.m_db.AddParameter("@EDATE", EDATE);
}
if (p_unit != "")
{
cmdTxt += @" and o.f_unit = @p_unit";
this.m_db.AddParameter("@p_unit", p_unit);
}
if (p_empid != "")
{
cmdTxt += @" and o.f_empid = @p_empid";
this.m_db.AddParameter("@p_empid", p_empid);
}
cmdTxt += @" group by o.f_unit,o.f_empid,o.f_editday,t.f_proname";
cmdTxt += @" order by o.f_editday";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
}
public void PlanningQuery(string p_unit, string p_empid, string p_companyid, DataTable p_dt)
{
string cmdTxt = @"select o.f_unit,o.f_editday,o.f_empid,t.f_proname,p.f_companyid";
cmdTxt += @" from TB_TONER_ORDER o";
cmdTxt += @" inner join TB_TONER_ORDERDETAIL t on t.f_orderno = o.f_orderno";
cmdTxt += @" left join TB_TONER_PROJECT p on p.f_proname = t.f_proname";
cmdTxt += @" where 1=1";
if (p_unit != "")
{
cmdTxt += @" and o.f_unit = @p_unit";
this.m_db.AddParameter("@p_unit", p_unit);
}
if (p_empid != "")
{
cmdTxt += @" and o.f_empid = @p_empid";
this.m_db.AddParameter("@p_empid", p_empid);
}
if (p_companyid != "")
{
cmdTxt += @" and o.f_companyid = @p_companyid";
this.m_db.AddParameter("@p_companyid", p_companyid);
}
cmdTxt += @" order by o.f_editday";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
}
/// <summary>
/// 判断是否转为经纪人
/// </summary>
/// <param name="sql">查询SQL</param>
/// <returns>bool</returns>
public static bool IsCanEconomicMan(string sql)
{
System.Data.Common.DbDataReader dbreader = null;
try
{
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
double countOccMoney = System.DBNull.Value != dbreader["countOccMoney"] ? Convert.ToDouble(dbreader["countOccMoney"]) : 0;
if (countOccMoney >= 0.1)
{
return(true);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(false);
}
public void QueryDatasBySPECIALSALE(DataTable dt, string bDate, string eDate, string orderno, string status)
{
string cmdTxt = @"select s.*,o.CustomerId, o.ordpnm,c.SFX,o.CarCod,o.yeartype,c.CarMdl";
cmdTxt += @" from TB_REW_SPECIALSALE s";
cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = s.ORDERNO";
cmdTxt += @" inner join TB_KD_CAR c on c.EngNo = o.EngNo";
cmdTxt += @" where s.TASK_RESULT = 0";
if (!"".Equals(bDate))
{
cmdTxt += " and s.CREATE_DATE between '" + bDate + "' and '" + eDate + "'";
}
if (!"".Equals(orderno))
{
cmdTxt += " and s.ORDERNO = '" + orderno + "'";
}
if (!"".Equals(status))
{
cmdTxt += " and s.STATUS = '" + status + "'";
}
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public DataTable getUserByGroupIDBy所廠(DataTable p_dt, string p_GroupID, string p_smid)
{
string cmdTxt = @" select u.USER_GUID,u.ACCOUNT,u.NAME,g.GROUP_NAME";
cmdTxt += @" from dbo.TB_EB_USER u";
cmdTxt += @" inner join dbo.TB_EB_EMPL_DEP d on u.USER_GUID = d.USER_GUID";
cmdTxt += @" inner join dbo.TB_EB_GROUP g on g.GROUP_ID = d.GROUP_ID";
if (String.IsNullOrEmpty(p_GroupID))
{
cmdTxt += @" where 1=1";
}
else
{
cmdTxt += @" where (g.PARENT_GROUP_ID = '" + p_GroupID + "' or g.GROUP_ID = '" + p_GroupID + "')";
}
cmdTxt += @" and u.EXPIRE_DATE='9999-12-31 23:59:59.997'";
if (!String.IsNullOrEmpty(p_smid))
{
cmdTxt += @" and u.Account = '" + p_smid + "'";
}
cmdTxt += @" order by u.ACCOUNT ";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
/// <summary>
/// 获取微交易用户UserID
/// </summary>
/// <param name="WUserId">WUserId</param>
/// <returns>bool</returns>
public static string GetWUserID(string WUserId)
{
System.Data.Common.DbDataReader dbreader = null;
string id = "";
try
{
dbreader = DbHelper.ExecuteReader(string.Format(@"select UserID from Base_WUser where WUserId='{0}'", WUserId));
while (dbreader.Read())
{
id = DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : "";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(id);
}
public DataTable Query(DataTable p_dt, string Bdate, string Edate, string p_smid, string p_GruopID, string p_GUID, string p_type)
{
string cmdTxt = @" select p.task_id,p.GUID,p.ASSESS_TYPE,g.GROUP_NAME,p.SMID,u.NAME,p.RANK,p.RANK_Y,p.EDIT_DATE,p.SMID+'@'+ p.ASSESS_TYPE+'@'+p.YEAR as keyword";
cmdTxt += @" from dbo.TB_HR_PERT p";
cmdTxt += @" left join TB_EB_USER u on u.ACCOUNT=p.SMID";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" where p.EDIT_DATE between '" + Bdate + "' and '" + Edate + "'";
if (!String.IsNullOrEmpty(p_type))
{
cmdTxt += @" and p.ASSESS_TYPE='" + p_type + "'";
}
if (!String.IsNullOrEmpty(p_smid))
{
cmdTxt += @" and p.SMID='" + p_smid + "'";
}
if (!String.IsNullOrEmpty(p_GruopID))
{
cmdTxt += @" and g.GROUP_ID in(" + p_GruopID.Substring(0, p_GruopID.Length - 1) + ")";
}
if (!String.IsNullOrEmpty(p_GUID))
{
cmdTxt += @" and p.GUID='" + p_GUID + "'";
}
cmdTxt += @" and e.ORDERS = 0";
cmdTxt += @" order by GROUP_NAME";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
public void QueryDatasByConditions(DataTable dt, string txtORDERNO, string txtNO)
{
string cmdTxt = @"select a.*,c.LicenseNo,p.PEO_NAME,c.EngNo ";
cmdTxt += @" from TB_PET_AGREE a";
cmdTxt += @" inner join TB_KD_ORDERS o on a.ORDERNO = o.OrderNo";
cmdTxt += @" inner join TB_KD_CAR c on o.EngNo = c.EngNo ";
cmdTxt += @" inner join TB_PET_PETITION p on p.ORDERNO = a.ORDERNO";
string conditionStr = "";
if (!string.IsNullOrEmpty(txtORDERNO))
{
conditionStr += "And a.ORDERNO = @ORDERNO";
this.m_db.AddParameter("@ORDERNO", txtORDERNO);
}
if (!string.IsNullOrEmpty(txtNO))//車主姓名
{
conditionStr += "And c.LicenseNo = @Name";
this.m_db.AddParameter("@Name", txtNO);
}
if (conditionStr.Length > 0)
{
cmdTxt += " where " + conditionStr.Substring(4);
}
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public void QueryDatasByAgreePrint(string licenseno, string orderno, string name, DataTable dt)
{
string cmdTxt = @"select p.KD_NUM,p.PEO_NAME,a.TYPE,c.LicenseNo,c.EngNo,s.NAME";
cmdTxt += @" from TB_PET_AGREE a";
cmdTxt += @" inner join TB_PET_PETITION p on p.ORDERNO = a.ORDERNO";
cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = a.ORDERNO";
cmdTxt += @" inner join TB_KD_CAR c on c.EngNo = o.EngNo";
cmdTxt += @" inner join TB_PET_SUPERVISE s on s.ID = p.PET_SUPID";
cmdTxt += @" where 1=1";
if (!"".Equals(licenseno))
{
cmdTxt += @" and c.LicenseNo = '" + licenseno + "'";
}
if (!"".Equals(orderno))
{
cmdTxt += @" and a.ORDERNO = '" + orderno + "'";
}
if (!"".Equals(name))
{
cmdTxt += @" and p.PEO_NAME = '" + name + "'";
}
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public DataTable getDatabyBranch(DataTable p_dt, string p_Branch, string p_ASSESS_TYPE, string p_year, string p_fromtype)
{
string cmdTxt = @" select h.SIGNER,f_A=IsNull(sum(case when h.RANK_Y ='A' then 1 end),0),";
cmdTxt += @" f_B1=IsNull(sum(case when h.RANK_Y ='B+' then 1 end),0),";
cmdTxt += @" f_B=IsNull(sum(case when h.RANK_Y ='B' then 1 end),0),";
cmdTxt += @" f_B3=IsNull(sum(case when h.RANK_Y ='B-' then 1 end),0),";
cmdTxt += @" f_C1=IsNull(sum(case when h.RANK_Y ='C+' then 1 end),0),";
cmdTxt += @" f_C=IsNull(sum(case when h.RANK_Y ='C' then 1 end),0),";
cmdTxt += @" f_C3=IsNull(sum(case when h.RANK_Y ='C-' then 1 end),0),";
cmdTxt += @" f_D1=IsNull(sum(case when h.RANK_Y ='D+' then 1 end),0),";
cmdTxt += @" f_D=IsNull(sum(case when h.RANK_Y ='D' then 1 end),0),";
cmdTxt += @" f_D3=IsNull(sum(case when h.RANK_Y ='D-' then 1 end),0),";
cmdTxt += @" f_F=IsNull(sum(case when h.RANK_Y ='F' then 1 end),0),";
cmdTxt += @" h.SITE_CODE,h.SITE_CODE+'/'+''+'/'+ g.GROUP_NAME+'/'+'" + p_fromtype + "' as keyword";
cmdTxt += @" from TB_HR_ASSESS_COMPTENT_DETAIL h";
cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" where g.GROUP_ID in( ";
cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
cmdTxt += @" and e.ORDERS = 0 ";
cmdTxt += @" and ASSESS_TYPE='" + p_ASSESS_TYPE + "'";
cmdTxt += @" and SUBSTRING(EDIT_DATE,1,4)='" + p_year + "'";
cmdTxt += @" group by h.SIGNER,h.SITE_CODE,g.GROUP_NAME";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
public void QueryDatas(DataTable dt)
{
string cmdTxt = @"select * from TB_REW_CARMODEL order by CAR_CODE,CAR_MDL,CAR_SFX";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public void QuerySALEDOCByREWARDDialog(DataTable dt, string Orderno)
{
string cmdTxt = @"select s.ORDERNO,s.INVOICE_MONEY,s.STATUS,o.BranchId,o.EngNo,o.SMId,o.SectorId,o.OrderDay,o.SaleDay,sp.SUPPORT_TYPE,";
cmdTxt += @" cm.CAR_PRICE,b.BASEAWARD,s.DISCOUNT_MONEY,s.SUBSIDIES_MONEY,sp.DISCOUNT,sp.SUPPORT_MONEY,sp.SPECIAL_MONEY";
cmdTxt += @" ,BIG_TYPE = IsNull((case when (isnull(sp.BIG_TYPE,'')= '0' ) then '無' ";
cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '1' ) then '計程車' ";
cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '2' ) then '租賃車(一般)' ";
cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '3' ) then '租賃車(和運)' ";
cmdTxt += @" end),0) ";
cmdTxt += @" ,SPECIAL_TYPE = IsNull((case when (isnull(sp.SPECIAL_TYPE,'')= '0' ) then '空車' ";
cmdTxt += @" when (isnull(sp.SPECIAL_TYPE,'')= '1' ) then '精裝' ";
cmdTxt += @" end),0),sp.M_APPLY_NUM,sp.APPLY_NUM ";
cmdTxt += @" from TB_REW_SALEDOC s ";
cmdTxt += @" inner join TB_REW_SPECIALSALE sp on sp.ORDERNO = s.ORDERNO";
cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = s.ORDERNO";
cmdTxt += @" inner join TB_REW_CARMODEL cm on cm.CAR_MDL = o.CarMdl and cm.CAR_SFX = o.SFX and cm.CAR_CODE= o.CarCod";
//cmdTxt += @" inner join TB_REW_BASEAWARD b on b.CAR_MDL = o.CarMdl and b.CAR_SFX = o.SFX and b.CAR_CODE= o.CarCod and b.CAR_YEAR = o.yeartype";
cmdTxt += @" inner join TB_REW_BASEAWARD b on b.CAR_MDL = o.CarMdl and b.CAR_SFX = o.SFX and b.CAR_CODE= o.CarCod ";
cmdTxt += @" where s.ORDERNO = '" + Orderno + "'";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public DataTable getDataforSevrice(DataTable p_dt, string p_TYPE, string p_GruopName, bool boPost, string p_ASSESS_TYPE, string p_year)
{
string cmdTxt = @" select SIGNER,f_A=IsNull(sum(case when RANK_Y ='A' then 1 end),0),";
cmdTxt += @" f_B1=IsNull(sum(case when RANK_Y ='B+' then 1 end),0),";
cmdTxt += @" f_B=IsNull(sum(case when RANK_Y ='B' then 1 end),0),";
cmdTxt += @" f_B3=IsNull(sum(case when RANK_Y ='B-' then 1 end),0),";
cmdTxt += @" f_C1=IsNull(sum(case when RANK_Y ='C+' then 1 end),0),";
cmdTxt += @" f_C=IsNull(sum(case when RANK_Y ='C' then 1 end),0),";
cmdTxt += @" f_C3=IsNull(sum(case when RANK_Y ='C-' then 1 end),0),";
cmdTxt += @" f_D1=IsNull(sum(case when RANK_Y ='D+' then 1 end),0),";
cmdTxt += @" f_D=IsNull(sum(case when RANK_Y ='D' then 1 end),0),";
cmdTxt += @" f_D3=IsNull(sum(case when RANK_Y ='D-' then 1 end),0),";
cmdTxt += @" f_F=IsNull(sum(case when RANK_Y ='F' then 1 end),0),";
cmdTxt += @" SITE_CODE,SITE_CODE +'/'+'" + p_TYPE.Replace("'", "") + "'+'/'+ GROUP_NAME as keyword"; //將原職務合併成一個欄位
//cmdTxt += @" SITE_CODE,SIGNER+'_'+SITE_CODE as keyword ,'" + p_TYPE.Replace("'", "") + "' as code";
cmdTxt += @" from TB_HR_ASSESS_SERVICE";
cmdTxt += @" where FUNC_NAME in (" + p_TYPE + ")";
if (boPost == true)
{
cmdTxt += @" and GROUP_NAME='" + p_GruopName + "'";
}
cmdTxt += @" and ASSESS_TYPE='" + p_ASSESS_TYPE + "'";
cmdTxt += @" and SUBSTRING(EDIT_DATE,1,4)='" + p_year + "'";
cmdTxt += @" group by SIGNER,SITE_CODE,GROUP_NAME";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
public DataTable Sevrice_Type(DataTable p_dt, string p_Branch, string p_SMID)
{
string cmdTxt = @" select h.SIGNER,f_A=IsNull(sum(case when h.RANK_Y ='A' then 1 end),0),";
cmdTxt += @" f_B1=IsNull(sum(case when h.RANK_Y ='B+' then 1 end),0),";
cmdTxt += @" f_B=IsNull(sum(case when h.RANK_Y ='B' then 1 end),0),";
cmdTxt += @" f_B3=IsNull(sum(case when h.RANK_Y ='B-' then 1 end),0),";
cmdTxt += @" f_C1=IsNull(sum(case when h.RANK_Y ='C+' then 1 end),0),";
cmdTxt += @" f_C=IsNull(sum(case when h.RANK_Y ='C' then 1 end),0),";
cmdTxt += @" f_C3=IsNull(sum(case when h.RANK_Y ='C-' then 1 end),0),";
cmdTxt += @" f_D1=IsNull(sum(case when h.RANK_Y ='D+' then 1 end),0),";
cmdTxt += @" f_D=IsNull(sum(case when h.RANK_Y ='D' then 1 end),0),";
cmdTxt += @" f_D3=IsNull(sum(case when h.RANK_Y ='D-' then 1 end),0),";
cmdTxt += @" f_F=IsNull(sum(case when h.RANK_Y ='F' then 1 end),0),";
cmdTxt += @" h.SITE_CODE,h.SIGNER+'_'+h.SITE_CODE as keyword";
cmdTxt += @" from TB_HR_ASSESS_SERVICE h";
cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" where g.GROUP_ID in ( ";
cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
cmdTxt += @" and h.ACCOUNT in ( ";
cmdTxt += @" " + p_SMID.Substring(0, p_SMID.Length - 1) + ")";
cmdTxt += @" group by h.SIGNER,h.SITE_CODE";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
/// <summary>
/// 获取交易用户信息
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static TradeUser GetTdUser(string sql)
{
System.Data.Common.DbDataReader dbreader = null;
TradeUser tdUser = new TradeUser();
try
{
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
tdUser.UserID = System.DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : string.Empty;
break;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(tdUser);
}
public void QueryDatasByQuery(DataTable dt, string BDate, string EDate, string orderno, string status, string smid)
{
string cmdTxt = @" select sp.ORDERNO,o.CustomerId,o.ordpnm,sp.CREATE_DATE,o.CarCod,o.CarMdl,o.yeartype,o.SFX,u.NAME,sp.TASK_ID";
cmdTxt += @" ,TASK_RESULT = IsNull((case";
cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '0' ) then '同意'";
cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '1' ) then '否決'";
cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '2' ) then '作廢'";
cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '9' ) then '未審核'";
cmdTxt += @" end),0)";
cmdTxt += @" from TB_REW_SPECIALSALE sp";
cmdTxt += @" inner join TB_KD_ORDERS o on sp.ORDERNO = o.OrderNo";
cmdTxt += @" left join TB_KD_CAR c on c.EngNo = o.EngNo";
cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = sp.CREATOR";
cmdTxt += @" where u.ACCOUNT = '" + smid + "'";
if (!"".Equals(BDate))
{
cmdTxt += " and sp.CREATE_DATE between '" + BDate + "' and '" + EDate + "'";
}
if (!"".Equals(orderno))
{
cmdTxt += " and sp.ORDERNO = '" + orderno + "'";
}
if (!"".Equals(status))
{
cmdTxt += " and isnull(sp.TASK_RESULT,'9') = '" + status + "'";
}
cmdTxt += @" order by CREATE_DATE";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public void QueryByDays(DataTable dt, DateTime p_BDate, DateTime p_EDate, string p_EmpNo)
{
string cmdTxt = " select a.*,b.DATE_TIME_MAX ,g.GROUP_NAME, t.TITLE_NAME from ";
cmdTxt += @" (select EMPLOYEE_ID,EMPLOYEE_EIP,MIN(DATE_TIME) as DATE_TIME_MIN ";
cmdTxt += @" from dbo.TB_HR_CLOCKTIME ";
cmdTxt += @" where EMPLOYEE_ID <> '' ";
if (!p_EmpNo.Equals(""))
{
cmdTxt += @" and EMPLOYEE_EIP = '" + p_EmpNo + "' ";
}
cmdTxt += @" and DATE_TIME >= '" + p_BDate + "' and DATE_TIME <='" + p_EDate + "' ";
cmdTxt += @" group by EMPLOYEE_ID,EMPLOYEE_EIP ) a ";
cmdTxt += @" join ";
cmdTxt += @" (select EMPLOYEE_ID,EMPLOYEE_EIP,MAX(DATE_TIME) as DATE_TIME_MAX ";
cmdTxt += @" from dbo.TB_HR_CLOCKTIME ";
cmdTxt += @" where EMPLOYEE_ID <> '' ";
if (!p_EmpNo.Equals(""))
{
cmdTxt += @" and EMPLOYEE_EIP = '" + p_EmpNo + "' ";
}
cmdTxt += @" and DATE_TIME >= '" + p_BDate + "' and DATE_TIME <='" + p_EDate + "' ";
cmdTxt += @" group by EMPLOYEE_ID,EMPLOYEE_EIP) b ";
cmdTxt += @" on a.EMPLOYEE_ID = b.EMPLOYEE_ID ";
cmdTxt += @" join TB_EB_USER u on b.EMPLOYEE_EIP = u.ACCOUNT ";
cmdTxt += @" join TB_EB_EMPL_DEP d on u.USER_GUID = d.USER_GUID ";
cmdTxt += @" join TB_EB_GROUP g on d.GROUP_ID = g.GROUP_ID ";
cmdTxt += @" join TB_EB_JOB_TITLE t on d.TITLE_ID = t.TITLE_ID ";
cmdTxt += @" ";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
///未完成，請補SQL
public void QueryQABySmid(DataTable dt, string smid, string closemonth, string status)
{
string cmdTxt = @"select *";
cmdTxt += @" from TB_REW_QA";
cmdTxt += @" where 1=1";
if (!"".Equals(smid))
{
cmdTxt += @" and CREATOR = '" + smid + "'";
}
if (!"".Equals(closemonth))
{
cmdTxt += @" and CLOSEMONTH = '" + closemonth + "'";
}
if (!"".Equals(smid))
{
cmdTxt += @" and CREATOR = '" + smid + "'";
}
if (!"".Equals(status))
{
cmdTxt += @" and STATUS = '" + status + "'";
}
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public DataTable getdatail(DataTable p_dt, string p_Bdate, string p_Edate, string p_smid, string p_GruopID, string p_GUID, string p_type)
{
string cmdTxt = @" select s.GUID,s.ASSESS_TYPE,s.SMID,u.NAME,t.TITLE_NAME,g.GROUP_NAME,s.RANK,s.RANK_Y,s.SITE_CODE,s.SIGNER";
cmdTxt += @" from TB_HR_ASSESS_GENERAL s";
cmdTxt += @" left join TB_EB_USER u on u.ACCOUNT=s.SMID";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" inner join TB_EB_JOB_TITLE t on t.TITLE_ID=e.TITLE_ID";
cmdTxt += @" where e.ORDERS = 0";
cmdTxt += @" and s.EDIT_DATE between '" + p_Bdate + "' and '" + p_Edate + "'";
if (!String.IsNullOrEmpty(p_smid))
{
cmdTxt += @" and s.SMID='" + p_smid + "'";
}
if (!String.IsNullOrEmpty(p_GruopID))
{
cmdTxt += @" and g.GROUP_ID in(" + p_GruopID.Substring(0, p_GruopID.Length - 1) + ")";
}
if (!String.IsNullOrEmpty(p_GUID))
{
cmdTxt += @" and s.GUID='" + p_GUID + "'";
}
if (!String.IsNullOrEmpty(p_type))
{
cmdTxt += @" and s.ASSESS_TYPE='" + p_type + "'";
}
cmdTxt += @" order by s.SMID";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
return(p_dt);
}
//public bool Post(DataTable p_dt, string p_SMID)
//{
// string cmdTxt = @" select FUNC_NAME";
// cmdTxt += @" from TB_EB_USER u";
// cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID=u.USER_GUID";
// cmdTxt += @" inner join TB_EB_JOB_FUNC jf on jf.FUNC_ID=ef.FUNC_ID";
// cmdTxt += @" where u.ACCOUNT='" + p_SMID + "'";
// System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
// p_dt.Load(dr); //解析SQL內的資料(可以是DataTable、DataGrid...)
// dr.Dispose(); //切斷與資料庫連線
// List<DataRow> l_list = new List<DataRow>();
// foreach (DataRow row in p_dt.Rows)
// {
// l_list.Add(row);
// }
// string l_str = "";
// for (int i = 0; i < l_list.Count; i++)
// {
// l_str = l_list[i][0].ToString();
// if (l_str.Equals("服務廠廠長"))
// {
// return true;
// }
// }
// return false;
//}
public bool Post(DataTable p_dt, string p_SMID, string p_post)
{
string cmdTxt = @" select FUNC_NAME";
cmdTxt += @" from TB_EB_USER u";
cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_JOB_FUNC jf on jf.FUNC_ID=ef.FUNC_ID";
cmdTxt += @" where u.ACCOUNT='" + p_SMID + "'";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr); //解析SQL內的資料(可以是DataTable、DataGrid...)
dr.Dispose(); //切斷與資料庫連線
List <DataRow> l_list = new List <DataRow>();
foreach (DataRow row in p_dt.Rows)
{
l_list.Add(row);
}
string l_str = "";
for (int i = 0; i < l_list.Count; i++)
{
l_str = l_list[i][0].ToString();
if (l_str.Equals(p_post))
{
return(true);
}
}
return(false);
}
public void QueryByREWReport(DataTable dt, string closeMonth, string smid)
{
string cmdTxt = @"select u.NAME,o.ordpnm,o.CarCod,o.CarMdl,o.SFX,o.yeartype,o.EngNo,o.OrderDay,o.SaleDay,rd.*";
cmdTxt += @" ,SUPPORT_TYPE = IsNull((case";
cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')in ('5','1') ) then '一般'";
cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')in ('2') ) then '僅核發'";
cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')='' ) then ''";
cmdTxt += @" end),0)";
cmdTxt += @" from dbo.TB_REW_REWARDDETAIL rd";
cmdTxt += @" left join TB_KD_ORDERS o on rd.ORDERNO = o.OrderNo";
cmdTxt += @" inner join TB_EB_USER u on rd.SMID = u.ACCOUNT";
cmdTxt += @" left join TB_REW_SPECIALSALE s on s.ORDERNO = rd.OrderNo ";
cmdTxt += @" where 1=1";
if (!"".Equals(closeMonth))
{
cmdTxt += " and rd.CLOSEMONTH = '" + closeMonth + "'";
}
if (!"".Equals(smid))
{
cmdTxt += " and rd.SMID = '" + smid + "'";
}
cmdTxt += @" order by rd.ORDERNO desc";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public double AMOUNTbySeason(DataTable p_dt, string p_GROUPID)
{
string cmdTxt = @" select Season = IsNull(sum(case when h.RANK='B' or h.RANK='A'then 1 end),0)";
cmdTxt += @" from TB_HR_ASSESS_GENERAL h";
cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
cmdTxt += @" where g.GROUP_ID='" + p_GROUPID + "'";
cmdTxt += @" and h.SITE_CODE='S2'";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr);
dr.Dispose();
List <DataRow> l_list = new List <DataRow>();
foreach (DataRow row in p_dt.Rows)
{
l_list.Add(row);
}
double l_int人數 = Convert.ToInt32(l_list[0][0].ToString());
return(l_int人數);
}
/// <summary>
/// 平仓延迟多少秒
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public double GetDelayFlatOrder(string userid)
{
System.Data.Common.DbDataReader dbreader = null;
double DelayFlatOrder = 0;
try
{
string sql = string.Format(@"select a.DelayFlatOrder from Trade_UserGroups a,Trade_User_Group b
where a.UserGroupId=b.UserGroupId and b.userid=@userid"); //使用参数化的sql语句，防止sql注入
dbreader = DbHelper.ExecuteReader(sql,
new System.Data.Common.DbParameter[]
{
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@userid", DbParameterType.String, userid, ParameterDirection.Input)
});
if (dbreader.Read())
{
DelayFlatOrder = Convert.ToDouble(dbreader["DelayFlatOrder"]);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(DelayFlatOrder);
}
public void getDatasByRewReport(DataTable dt, string closemonth, string brandid, string smid)
{
string cmdTxt = @"select r.*,u.NAME,g.PARENT_GROUP_ID,g.GROUP_NAME";
cmdTxt += @" from dbo.TB_REW_REWARD r";
cmdTxt += @" inner join TB_EB_USER u on r.SMID = u.ACCOUNT";
cmdTxt += @" inner join TB_EB_EMPL_DEP d on d.USER_GUID = u.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on d.GROUP_ID = g.GROUP_ID";
cmdTxt += @" where 1=1";
if (!"".Equals(closemonth))
{
cmdTxt += @" and r.CLOSEMONTH ='" + closemonth + "'";
}
if (!"".Equals(brandid))
{
cmdTxt += @" and g.PARENT_GROUP_ID ='" + brandid + "'";
}
if (!"".Equals(smid))
{
cmdTxt += @" and r.SMID ='" + smid + "'";
}
cmdTxt += @" order by g.GROUP_NAME";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
public DataTable getDetailbyDate(DataTable p_dt, string p_BDate, string p_EDate)
{
string cmdTxt = @"select u.ACCOUNT,u.NAME,p.ARRIVE_DATE,p.BIRTHDAY,eh.MILITARY_SERVICE,he.SCHOOL,he.MAJOR,ns.CLASS,";
cmdTxt += @" ns.MOVE_DATE,ns.MOVE_TYPE,ns.Group_Name,ns.TITLE_NAME,ns.OPTION1,ns.Base_Salary,ns.SINGER,ns.TASK_ID,";
cmdTxt += @" ns.Food_Allowance,ns.Full_Bonus,ns.Position_Bonus,ns.Learder_Bonus,ns.Skill_Bonus,ns.License_Bonus,";
cmdTxt += @" ns.Experience_Bonus,ns.Other_Bonus,ns.Bonus_Sum,ns.Creat_Date,ns.MEMO from (";
cmdTxt += @" select USER_GUID,CLASS,Creat_Date,MOVE_DATE,MOVE_TYPE,Group_Name,TITLE_NAME,";
cmdTxt += @" OPTION1, Base_Salary,Food_Allowance,Full_Bonus,Position_Bonus,Learder_Bonus,TASK_ID,";
cmdTxt += @" Skill_Bonus,License_Bonus,Experience_Bonus,Other_Bonus,Bonus_Sum,SINGER ,MEMO";
cmdTxt += @" from TB_HR_Nuclear_Salary ) as ns ";
cmdTxt += @" left join TB_EB_USER u on u.USER_GUID =ns.USER_GUID";
cmdTxt += @" left join TB_EB_EMPL p on p.USER_GUID=ns.USER_GUID";
cmdTxt += @" left join TB_EB_EMPL_HR_EDUACTION he on he.USER_GUID = p.USER_GUID";
cmdTxt += @" left join TB_EB_EMPL_DEP ed on ed.USER_GUID = p.USER_GUID";
cmdTxt += @" left join TB_EB_JOB_TITLE jt on jt.TITLE_ID = ed.TITLE_ID";
cmdTxt += @" left join TB_EB_EMPL_HR eh on eh.USER_GUID = p.USER_GUID";
cmdTxt += @" where ed.ORDERS = 0";
cmdTxt += @" and ns.MOVE_DATE between '" + p_BDate + "' and '" + p_EDate + "'";
cmdTxt += @" order by u.ACCOUNT desc,ns.Creat_Date,ns.MOVE_DATE";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
p_dt.Load(dr); //解析SQL內的資料(可以是DataTable、DataGrid...)
dr.Dispose(); //切斷與資料庫連線
return(p_dt);
}
/// <summary>
/// 判断会员是否能承接头寸
/// </summary>
/// <param name="orgid">组织机构ID</param>
/// <param name="yingkui">盈亏</param>
/// <param name="money">输出参数，如返回true则该值会员账户的余额，如返回false则该值为平台账户的余额</param>
/// <returns>bool:true会员承接头寸;false平台承接头寸</returns>
public static bool IsCanOrgTrade(string orgid, double yingkui, ref double money)
{
bool result = true;//默认为会员承担头寸
System.Data.Common.DbDataReader dbreader = null;
try
{
string sql = string.Format(@"select IsTrade from Base_Org where OrgID='{0}'", orgid);
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
bool IsTrade = System.DBNull.Value != dbreader["IsTrade"] ? Convert.ToBoolean(dbreader["IsTrade"]) : false;
if (IsTrade == false) //会员不承担头寸，则由平台承担
{
result = false; //会员不承担头寸
//获取平台账户余额
sql = string.Format(@"select money from Trade_OrgFund where OrgID='system'");
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
}
}
else//会员承担头寸
{
//获取会员账户余额
sql = string.Format(@"select money from Trade_OrgFund where OrgID='{0}'", orgid);
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
if (money < yingkui) //如果此时会员账户余额不足以支付客户盈利，则由平台承接头寸
{
result = false; //会员单位不承担头寸，由平台承接
sql = string.Format(@"select money from Trade_OrgFund where OrgID='system'");
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
}
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(result);
}
public void QuerySaleOROrederCnt(DataTable dt, string BDate, string EDate, string type)
{
string cmdTxt = @"";
cmdTxt += @" select 'GROUP_NAME'=g.GROUP_NAME, 'ACCOUNT'=u.ACCOUNT,'NAME'=u.NAME,'ARRIVE_DATE' = em.ARRIVE_DATE,";
cmdTxt += @" 'CAMRY'=isnull(o.camry,0),'CAMRYH'=isnull(o.CAMRYH,0),'WISH'=isnull(o.WISH,0),";
cmdTxt += @" 'ALTIS'=isnull(o.ALTIS,0),'VIOS'=isnull(o.VIOS,0),";
cmdTxt += @" 'PREVIA'=isnull(o.PREVIA,0),'PRIUS'=isnull(o.PRIUS,0),'PRIUSC'=isnull(o.PRIUSC,0),";
cmdTxt += @" 'YARIS'=isnull(o.YARIS,0),'INNOVA'=isnull(o.INNOVA,0),'RAV4'=isnull(o.RAV4,0)";
cmdTxt += @" ,'ALPHARD'=isnull(o.ALPHARD,0),'PRADO'=isnull(o.PRADO,0),'86'=isnull(o.AE86,0),'cnt'=isnull(o.cnt,0)";
cmdTxt += @" from TB_EB_USER u";
cmdTxt += @" inner join TB_EB_EMPL_DEP e on u.USER_GUID = e.USER_GUID";
cmdTxt += @" inner join TB_EB_EMPL em on u.USER_GUID = em.USER_GUID";
cmdTxt += @" inner join TB_EB_GROUP g on e.GROUP_ID = g.GROUP_ID";
cmdTxt += @" inner join TB_EB_JOB_TITLE j on e.TITLE_ID = j.TITLE_ID";
cmdTxt += @" left join (";
//先查出訂單檔資料再left join給TB_EB_USER(沒業績的業務也要顯現)
cmdTxt += @" select smid,";
cmdTxt += @" CAMRY=sum(case when o.carcod = 'K' then 1 else 0 end),";
cmdTxt += @" WISH=sum(case when o.carcod = 'W' then 1 else 0 end),";
cmdTxt += @" ALTIS=sum(case when o.carcod = 'V' then 1 else 0 end),";
cmdTxt += @" VIOS=sum(case when o.carcod = 'O' then 1 else 0 end),";
cmdTxt += @" PREVIA=sum(case when o.carcod = 'U' then 1 else 0 end),";
cmdTxt += @" PRIUS=sum(case when o.carcod = 'PS' then 1 else 0 end),";
cmdTxt += @" YARIS=sum(case when o.carcod = 'YS' then 1 else 0 end),";
cmdTxt += @" INNOVA=sum(case when o.carcod = 'IN' then 1 else 0 end),";
cmdTxt += @" RAV4=sum(case when o.carcod = 'R' then 1 else 0 end),";
cmdTxt += @" CAMRYH=sum(case when o.carcod = 'KH' then 1 else 0 end),";
cmdTxt += @" PRIUSC=sum(case when o.carcod = 'PC' then 1 else 0 end),";
cmdTxt += @" ALPHARD=sum(case when carcod = 'AL' then 1 else 0 end),";
cmdTxt += @" PRADO=sum(case when carcod = 'PD' then 1 else 0 end),";
cmdTxt += @" AE86=sum(case when carcod = '86' then 1 else 0 end),";
cmdTxt += @" cnt = count(*)";
cmdTxt += @" from TB_KD_ORDERS o";
if ("受訂日".Equals(type))
{
cmdTxt += @" where orderday between '" + BDate + "' and '" + EDate + "'";
cmdTxt += @" and status <=5";
}
else
{
cmdTxt += @" where saleday between '" + BDate + "' and '" + EDate + "'";
cmdTxt += @" and status between '3' and '5'";
}
cmdTxt += @" group by smid";
cmdTxt += @" ) o on o.smid = u.ACCOUNT";
//離職的不抓
cmdTxt += @" where u.EXPIRE_DATE = '9999-12-31 23:59:59.997'";
//只抓營業所
cmdTxt += @" and g.LEV = 5";
//只抓業代
cmdTxt += @" and j.TITLE_NAME in ('課長級銷售顧問','銷售主任','銷售副理','銷售經理','高級銷售顧問','副理級銷售顧問','經理級銷售顧問','銷售襄理','銷售專員','銷售課長','銷售顧問')";
cmdTxt += @" order by g.GROUP_NAME,u.ACCOUNT";
System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
dt.Load(dr);
dr.Dispose();
}
