public void insert(CWork p_data) { string l_Sql = ""; l_Sql += "INSERT INTO [KG].[dbo].[tbWork](f_workid,f_engo,f_customerid,f_workType"; l_Sql += " ,f_money,f_memo,f_edituser,f_editdate,f_closeDate,f_introducer,f_type"; l_Sql += " ,f_insuranceid,f_insurancetype,f_staus,f_branchid,f_seruser) VALUES ("; l_Sql += " '" + p_data.f_workid工單單號 + "'"; l_Sql += ", '" + p_data.f_engo引擎號碼 + "'"; l_Sql += ", '" + p_data.f_customerid顧客名稱 + "'"; l_Sql += ", '" + p_data.f_workType洗車種類 + "'"; l_Sql += ", " + p_data.f_money金額 + ""; l_Sql += ", '" + p_data.f_memo備註 + "'"; l_Sql += ", '" + p_data.f_edituser開單人員 + "'"; l_Sql += ", '" + p_data.f_editdate開單日期 + "'"; l_Sql += ", '" + p_data.f_closeDate完工日 + "'"; l_Sql += ", '" + p_data.f_introducer介紹人 + "'"; l_Sql += ", '" + p_data.f_type工單種類 + "'"; l_Sql += ", '" + p_data.f_insuranceid保險代碼 + "'"; l_Sql += ", '" + p_data.f_insurancetype保險型態 + "'"; l_Sql += ", '" + p_data.f_staus狀態 + "'"; l_Sql += ", '" + p_data.f_branchid工單所別 + "'"; l_Sql += ", '" + p_data.f_seruser服務專員 + "'"; l_Sql += ")"; ivContext.資料管理員.excuteSqlNonquery(l_Sql); }
public int get工單資訊By業績(string p_strsmid, string p_strBDate, string p_strEDate) { string l_Sql = ""; //fix by fox //l_Sql += "select sum(f_money) as f_money from tbWork "; //l_Sql += "where f_workid in"; //l_Sql += " (select f_workid from tbConstruction where f_smid = '" + p_strsmid + "')"; l_Sql += " select sum(w.f_money) as f_money "; l_Sql += " from tbConstruction as c "; l_Sql += " join tbwork as w on c.f_workid = w.f_workid "; l_Sql += " where 1 = 1 "; l_Sql += " and f_smid = '" + p_strsmid + "' "; if (!"".Equals(p_strBDate)) { l_Sql += " and f_closeDate between '" + p_strBDate + "' and '" + p_strEDate + "'"; } l_Sql += " group by c.f_smid "; //fix by fox DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_Sql); CWork[] l_datas = new CWork[l_dv.Count]; if (l_dv != null) { for (int i = 0; i < l_datas.Length; i++) { l_datas[i] = createCWork(); l_datas[i].f_money金額 = Convert.ToInt32(l_dv[i]["f_money"].ToString()); } } return(l_datas[0].f_money金額); }
private CWork[] queryBySql(string p_sql) { DataView l_dv = ivContext.資料管理員.getDataViewBySql(p_sql); CWork[] l_datas = new CWork[l_dv.Count]; if (l_dv != null) { for (int i = 0; i < l_datas.Length; i++) { l_datas[i] = createCWork(); l_datas[i].f_workid工單單號 = l_dv[i]["f_workid"].ToString(); l_datas[i].f_engo引擎號碼 = l_dv[i]["f_engo"].ToString(); l_datas[i].f_customerid顧客名稱 = l_dv[i]["f_customerid"].ToString(); l_datas[i].f_workType洗車種類 = l_dv[i]["f_workType"].ToString(); l_datas[i].f_money金額 = Convert.ToInt32(l_dv[i]["f_money"].ToString()); l_datas[i].f_memo備註 = l_dv[i]["f_memo"].ToString(); l_datas[i].f_edituser開單人員 = l_dv[i]["f_edituser"].ToString(); l_datas[i].f_editdate開單日期 = l_dv[i]["f_editdate"].ToString(); l_datas[i].f_closeDate完工日 = l_dv[i]["f_closeDate"].ToString(); l_datas[i].f_introducer介紹人 = l_dv[i]["f_introducer"].ToString(); l_datas[i].f_type工單種類 = l_dv[i]["f_type"].ToString(); l_datas[i].f_insuranceid保險代碼 = l_dv[i]["f_insuranceid"].ToString(); l_datas[i].f_staus狀態 = l_dv[i]["f_staus"].ToString(); l_datas[i].f_branchid工單所別 = l_dv[i]["f_branchid"].ToString(); l_datas[i].f_seruser服務專員 = l_dv[i]["f_seruser"].ToString(); } return(l_datas); } return(null); }
public CWork[] get工單資訊By施工人員檔(CWork p_CWork) { string l_Sql = ""; l_Sql += "select * from tbWork where f_workid in ("; for (int i = 0; i < p_CWork.tb施工人員業績.Length; i++) { l_Sql += " '" + p_CWork.tb施工人員業績[i].f_workid工單單號 + "',"; } l_Sql = l_Sql.Substring(0, l_Sql.Length - 1); l_Sql += ")"; l_Sql += " order by f_closeDate"; return(queryBySql(l_Sql)); }
public void update(CWork p_data) { string l_Sql = ""; l_Sql += "UPDATE [KG].[dbo].[tbWork]"; l_Sql += " SET [f_engo] ='" + p_data.f_engo引擎號碼 + "'"; l_Sql += ",[f_customerid] ='" + p_data.f_customerid顧客名稱 + "'"; l_Sql += ",[f_workType] ='" + p_data.f_workType洗車種類 + "'"; l_Sql += " ,[f_money]=" + p_data.f_money金額 + ""; l_Sql += ",[f_memo] ='" + p_data.f_memo備註 + "'"; l_Sql += ",[f_edituser] ='" + p_data.f_edituser開單人員 + "'"; l_Sql += ",[f_editdate] ='" + p_data.f_editdate開單日期 + "'"; l_Sql += ",[f_closeDate] ='" + p_data.f_closeDate完工日 + "'"; l_Sql += ",[f_introducer] ='" + p_data.f_introducer介紹人 + "'"; l_Sql += ",[f_type] ='" + p_data.f_type工單種類 + "'"; l_Sql += ",[f_staus] ='" + p_data.f_staus狀態 + "'"; l_Sql += ",[f_branchid] ='" + p_data.f_branchid工單所別 + "'"; l_Sql += " WHERE [f_workid] = '" + p_data.f_workid工單單號 + "'"; ivContext.資料管理員.excuteSqlNonquery(l_Sql); }
public CWork get判斷是否有同一天重複開單(string p_引擎號碼, string p_日期) { string l_Sql = ""; l_Sql += "select f_edituser from tbWork where f_engo = '" + p_引擎號碼 + "' and substring(f_editdate,0,11) ='" + p_日期 + "'"; DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_Sql); CWork[] l_datas = new CWork[l_dv.Count]; if (l_datas.Length > 0) { for (int i = 0; i < l_datas.Length; i++) { l_datas[i] = createCWork(); l_datas[i].f_edituser開單人員 = l_dv[i]["f_edituser"].ToString(); } return(l_datas[0]); } return(null); }
public CWork[] get工單資訊By管理者(string p_BDate, string p_EDate, string p_str工單號碼, string p_所別, string p_是否完工, string p_str工單類型, string p_str是否招攬, bool p_bool服務廠, string p_str車牌或引擎, bool p_bool和榮) { string l_Sql = ""; l_Sql += "select w.* ,t.name from tbWork w"; l_Sql += " left join kdnews..T_HRPersonel t on w.f_edituser = t.emp_id"; l_Sql += " where 1 = 1"; if (!"".Equals(p_str工單號碼)) { l_Sql += " and w.f_workid = '" + p_str工單號碼 + "'"; } if (!"".Equals(p_所別)) { l_Sql += " and w.f_branchid ='" + p_所別 + "'"; } //Y:完工 ,N:未完工 判斷欄位f_closedate if ("Y".Equals(p_是否完工)) { if (!"".Equals(p_BDate)) { l_Sql += " and w.f_closedate between '" + p_BDate + "' and '" + p_EDate + "'"; } else { l_Sql += " and w.f_closedate <> '' "; } } else if ("N".Equals(p_是否完工)) { if (!"".Equals(p_BDate)) { l_Sql += " and substring(w.f_editdate,0,11) between '" + p_BDate + "' and '" + p_EDate + "'"; } l_Sql += " and w.f_closedate = '' "; } else { if (!"".Equals(p_BDate)) { l_Sql += " and substring(w.f_editdate,0,11) between '" + p_BDate + "' and '" + p_EDate + "'"; } } if (!"".Equals(p_str工單類型)) { l_Sql += " and w.f_type ='" + p_str工單類型 + "'"; } //和榮只看D00 if (p_bool和榮) { l_Sql += " and w.f_worktype like 'D00%'"; } //Y:完工 ,N:未完工 判斷欄位f_closedate if ("Y".Equals(p_str是否招攬)) { l_Sql += " and w.f_introducer <> '' "; } else if ("N".Equals(p_str是否招攬)) { l_Sql += " and w.f_introducer = '' "; } //服務廠業績項目是三種,富貴要看的 if (p_bool服務廠) { l_Sql += " and w.f_type in ('一般','保險','員工')"; } if (!"".Equals(p_str車牌或引擎)) { l_Sql += " and w.f_engo ='" + p_str車牌或引擎 + "'"; } l_Sql += " order by w.f_branchid,w.f_editdate"; DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_Sql); CWork[] l_datas = new CWork[l_dv.Count]; if (l_dv != null) { for (int i = 0; i < l_datas.Length; i++) { l_datas[i] = createCWork(); l_datas[i].f_workid工單單號 = l_dv[i]["f_workid"].ToString(); l_datas[i].f_engo引擎號碼 = l_dv[i]["f_engo"].ToString(); l_datas[i].f_customerid顧客名稱 = l_dv[i]["f_customerid"].ToString(); l_datas[i].f_workType洗車種類 = l_dv[i]["f_workType"].ToString(); l_datas[i].f_money金額 = Convert.ToInt32(l_dv[i]["f_money"].ToString()); l_datas[i].f_memo備註 = l_dv[i]["f_memo"].ToString(); l_datas[i].f_edituser開單人員 = l_dv[i]["f_edituser"].ToString(); l_datas[i].f_editdate開單日期 = l_dv[i]["f_editdate"].ToString(); l_datas[i].f_closeDate完工日 = l_dv[i]["f_closeDate"].ToString(); l_datas[i].f_introducer介紹人 = l_dv[i]["f_introducer"].ToString(); l_datas[i].f_type工單種類 = l_dv[i]["f_type"].ToString(); l_datas[i].f_insuranceid保險代碼 = l_dv[i]["f_insuranceid"].ToString(); l_datas[i].f_staus狀態 = l_dv[i]["f_staus"].ToString(); l_datas[i].f_branchid工單所別 = l_dv[i]["f_branchid"].ToString(); l_datas[i].f_seruser服務專員 = l_dv[i]["f_seruser"].ToString(); l_datas[i].f_edituser開單人員 = l_dv[i]["name"].ToString(); } return(l_datas); } return(null); }