private CKGPointDetail[] queryBySql(string p_sql) { DataView l_dv = ivContext.資料管理員.getDataViewBySql(p_sql); List <CKGPointDetail> l_datas = new List <CKGPointDetail>(); if (l_dv.Count > 0) { for (int i = 0; i < l_dv.Count; i++) { CKGPointDetail l_code = createCKGPointDetail(); l_code.f_id = Convert.ToInt32(l_dv[i]["f_id"].ToString()); l_code.f_Smid業代員編 = l_dv[i]["f_Smid"].ToString(); l_code.f_Name業代姓名 = l_dv[i]["f_Name"].ToString(); l_code.f_ImportPoint匯入點數 = Convert.ToInt32(l_dv[i]["f_ImportPoint"].ToString()); l_code.f_ImportDate匯入日期 = l_dv[i]["f_ImportDate"].ToString(); l_code.f_ImportSmid匯入人員員編 = l_dv[i]["f_ImportSmid"].ToString(); l_code.f_ImportName匯入人員姓名 = l_dv[i]["f_ImportName"].ToString(); l_code.f_ImportType匯入方式 = l_dv[i]["f_ImportType"].ToString(); l_code.f_InvoiceNo發票號碼 = l_dv[i]["f_InvoiceNo"].ToString(); l_code.f_PayBank刷卡銀行 = l_dv[i]["f_PayBank"].ToString(); l_code.f_BankCharge手續費 = Convert.ToInt32(l_dv[i]["f_BankCharge"].ToString()); l_code.f_InMoneyBank入帳銀行 = l_dv[i]["f_InMoneyBank"].ToString(); l_datas.Add(l_code); } return(l_datas.ToArray()); } else { return(null); } }
public void updateCKGPointDetail(CKGPointDetail p_code) { string l_strSql = "UPDATE [KG].[dbo].[tbKGPointDetail] SET "; l_strSql += "[f_Smid] = '" + p_code.f_Smid業代員編 + "',"; l_strSql += "[f_Name] = N'" + p_code.f_Name業代姓名 + "',"; l_strSql += "[f_ImportPoint] = '" + p_code.f_ImportPoint匯入點數 + "',"; l_strSql += "[f_ImportDate] = '" + p_code.f_ImportDate匯入日期 + "',"; l_strSql += "[f_ImportSmid] = '" + p_code.f_ImportSmid匯入人員員編 + "',"; l_strSql += "[f_ImportName] = N'" + p_code.f_ImportName匯入人員姓名 + "',"; l_strSql += "[f_ImportType] = '" + p_code.f_ImportType匯入方式 + "',"; l_strSql += "[f_InvoiceNo] = '" + p_code.f_InvoiceNo發票號碼 + "'"; l_strSql += " WHERE f_id = '" + p_code.f_id + "'"; ivContext.資料管理員.excuteSqlNonquery(l_strSql); }
public void insertCKGPointDetail(CKGPointDetail p_code) { string l_strSql = "INSERT INTO [KG].[dbo].[tbKGPointDetail] ([f_Smid],[f_Name],[f_ImportPoint],[f_ImportDate],[f_ImportSmid],[f_ImportName],[f_ImportType],[f_InvoiceNo],[f_PayBank],[f_BankCharge],[f_InMoneyBank],[f_Memo])VALUES("; l_strSql += " '" + p_code.f_Smid業代員編 + "' "; l_strSql += ", N'" + p_code.f_Name業代姓名 + "' "; l_strSql += ", '" + p_code.f_ImportPoint匯入點數 + "' "; l_strSql += ", '" + p_code.f_ImportDate匯入日期 + "' "; l_strSql += ", '" + p_code.f_ImportSmid匯入人員員編 + "' "; l_strSql += ", N'" + p_code.f_ImportName匯入人員姓名 + "' "; l_strSql += ", '" + p_code.f_ImportType匯入方式 + "' "; l_strSql += ", '" + p_code.f_InvoiceNo發票號碼 + "' "; l_strSql += ", '" + p_code.f_PayBank刷卡銀行 + "' "; l_strSql += ", '" + p_code.f_BankCharge手續費 + "' "; l_strSql += ", '" + p_code.f_InMoneyBank入帳銀行 + "' "; l_strSql += ", '" + p_code.f_Memo + "' "; l_strSql += ")"; ivContext.資料管理員.excuteSqlNonquery(l_strSql); }
/// <summary> /// (Yu 20090515) 取得資料庫內所有的匯入模式 (DISTINCT f_ImportType) /// </summary> /// <returns></returns> public CKGPointDetail[] get匯入模式() { string l_strSql = "SELECT DISTINCT f_ImportType FROM [KG].[dbo].[tbKGPointDetail]"; DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql); List <CKGPointDetail> l_datas = new List <CKGPointDetail>(); if (l_dv.Count > 0) { for (int i = 0; i < l_dv.Count; i++) { CKGPointDetail l_code = createCKGPointDetail(); l_code.f_ImportType匯入方式 = l_dv[i]["f_ImportType"].ToString(); l_datas.Add(l_code); } return(l_datas.ToArray()); } else { return(null); } }
/// <summary> /// (Yu 20090515) 取得點數匯入記錄資料By條件 /// </summary> /// <param name="p_str業代員編"></param> /// <param name="p_str匯入方式"></param> /// <param name="p_strStartDay"></param> /// <param name="p_strEndDay"></param> /// <returns></returns> public CKGPointDetail[] getCKGPointDetailBy條件(string p_str所別, string p_str業代員編, string p_str匯入方式, string p_str入帳銀行, string p_strStartDay, string p_strEndDay) { string l_strSql = "SELECT f_id,f_Smid,f_Name,Branch='F'+Substring(T.Branchid,5,2),f_ImportPoint,f_ImportDate,f_ImportSmid,f_ImportName,f_ImportType,f_InMoneyBank,f_InvoiceNo,f_PayBank,f_BankCharge, f_Memo"; l_strSql += " FROM [KG].[dbo].[tbKGPointDetail] K "; l_strSql += " INNER JOIN [kdnews].[dbo].[T_HRPersonel] T "; l_strSql += " ON K.f_Smid = T.Emp_id WHERE 1=1"; if (!"".Equals(p_str所別)) { l_strSql += " AND 'F'+ Substring(T.Branchid,5,2) ='" + p_str所別 + "'"; } if (!"".Equals(p_str業代員編)) { l_strSql += " AND K.f_Smid ='" + p_str業代員編 + "'"; } if (!"".Equals(p_str匯入方式)) { l_strSql += " AND K.f_ImportType ='" + p_str匯入方式 + "'"; } if (!"".Equals(p_str入帳銀行)) { l_strSql += " AND K.f_InMoneyBank ='" + p_str入帳銀行 + "'"; } if (!"".Equals(p_strStartDay) && "".Equals(p_strEndDay)) { l_strSql += " AND K.f_ImportDate >='" + p_strStartDay + "'"; } else if ("".Equals(p_strStartDay) && !"".Equals(p_strEndDay)) { l_strSql += " AND K.f_ImportDate <='" + p_strEndDay + "'"; } else if (!"".Equals(p_strStartDay) && !"".Equals(p_strEndDay)) { l_strSql += " AND K.f_ImportDate BETWEEN '" + p_strStartDay + " 00:00:00' AND '" + p_strEndDay + " 23:59:59'"; } l_strSql += " ORDER BY Branch,K.f_Smid,K.f_ImportDate"; DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql); List <CKGPointDetail> l_datas = new List <CKGPointDetail>(); if (l_dv.Count > 0) { for (int i = 0; i < l_dv.Count; i++) { CKGPointDetail l_code = createCKGPointDetail(); l_code.f_id = Convert.ToInt32(l_dv[i]["f_id"].ToString()); l_code.f_Smid業代員編 = l_dv[i]["f_Smid"].ToString(); l_code.f_Name業代姓名 = l_dv[i]["f_Name"].ToString(); l_code.Branch所別 = l_dv[i]["Branch"].ToString(); l_code.f_ImportPoint匯入點數 = Convert.ToInt32(l_dv[i]["f_ImportPoint"].ToString()); l_code.f_ImportDate匯入日期 = l_dv[i]["f_ImportDate"].ToString(); l_code.f_ImportSmid匯入人員員編 = l_dv[i]["f_ImportSmid"].ToString(); l_code.f_ImportName匯入人員姓名 = l_dv[i]["f_ImportName"].ToString(); l_code.f_ImportType匯入方式 = l_dv[i]["f_ImportType"].ToString(); l_code.f_InvoiceNo發票號碼 = l_dv[i]["f_InvoiceNo"].ToString(); l_code.f_PayBank刷卡銀行 = l_dv[i]["f_PayBank"].ToString(); l_code.f_BankCharge手續費 = Convert.ToInt32(l_dv[i]["f_BankCharge"].ToString()); l_code.f_InMoneyBank入帳銀行 = l_dv[i]["f_InMoneyBank"].ToString(); l_code.f_Memo = l_dv[i]["f_Memo"].ToString(); l_datas.Add(l_code); } return(l_datas.ToArray()); } else { return(null); } }