public DataTable getReservationLeave(string Date) { DataTable dt = new DataTable(); dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from vw_ReservationLeave where workdate='" + Date + "'"); return(dt); }
/// <summary> /// 要根据ContractNum,而不是Amadeus预订号关联 /// </summary> public void BuddyAnalysis() { string sql = @"select distinct d.custid,ContractNum from crmCustomerDeal d where ContractNum in ( select ContractNum from crmCustomerDeal group by ContractNum having count(custID)>1 )"; DataTable dt = DBExtBase.ExeFillTblBySqlText(dataCtx, sql); for (int i = 0; i < dt.Rows.Count; i++) { string custID = dt.Rows[i]["custid"].ToString(); string ContractNum = dt.Rows[i]["ContractNum"].ToString(); string oldBuddyList = DBExtBase.ExeScalarBySqlText(dataCtx, "select buddylist from CRMCustomer where custID=" + custID).ToString(); string[] arrOldBuddy = oldBuddyList.Split('|'); //同一张单的其他人 DataRow[] arr = dt.Select("ContractNum='" + ContractNum + "' and custid<>" + custID); string buddylist = ""; for (int j = 0; j < arr.Length; j++) { if (arrOldBuddy.Contains(arr[j]["custid"]) == false) { buddylist += arr[j]["custid"] + "|"; } } if (buddylist.Length > 0) { buddylist = buddylist.Substring(0, buddylist.Length - 1); DBExtBase.ExeNonQueryBySqlText(dataCtx, "update CRMCustomer set BuddyList='" + buddylist + "' where custID=" + custID); } } }
public DataTable ReadLog(DateTime eventDate) { DataTable dt = new DataTable(); dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from tblLog where eventDate between '" + eventDate.ToString() + "' and '" + eventDate.AddDays(1).ToString() + "'"); return(dt); }
public DataTable LoadAllEmployeeForReservation() { DataTable dt = new DataTable(); dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select employeeNum,employeeName from tblEmployee "); return(dt); }
public string getBusinessExcludeHours(string strEmployeeNum, DateTime workDate) { DataTable table = new DataTable(); string sql = "SELECT * FROM tblReservationTime"; sql += " where employeeNum='" + strEmployeeNum + "'"; sql += " and workdate ='" + workDate.ToShortDateString() + "'"; table = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); string BusinessExcludeHours = ""; if (table.Rows.Count > 0) { //如果休息,整日都变成灰色 if ((bool)table.Rows[0]["isLeave"] == true) { BusinessExcludeHours = "11:00-23:00"; } else { BusinessExcludeHours = "11:00-" + table.Rows[0]["checkinTime"] + "," + table.Rows[0]["checkoutTime"] + "-23:00"; } } return(BusinessExcludeHours); }
public DataTable GetCustNameTel(enumRoleGrade LoginUserRoleGrade, long LoginUserID, long LoginUserRoleID) { //add search criteria string Filter = string.Empty; //Chemical: sales只能看到自己所属的客户,产品经理可以看到有使用其所属产品的客户,老板可以看到所有 //Ticket: sales能看到所有的客户 switch (LoginUserRoleGrade) { case enumRoleGrade.Sales: //sales Filter = "and CustOwnerID=" + LoginUserID; break; case enumRoleGrade.ProdManager: //Prod Manager can see its prods and it's customer as sales Filter = "and CustId in (select CustId from vw_CRMRoleCustomer where RoleId=" + LoginUserRoleID.ToString() + ")"; break; case enumRoleGrade.DepManager: //Dep Manager can see its department customers Filter = "and CustId in (select CustId from vw_CRMRoleDepCustomer where RoleId=" + LoginUserRoleID.ToString() + ")"; break; case enumRoleGrade.Boss: //Boss Filter = "and 1=1"; break; default: Filter = "and 1=0"; break; } return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, @"select Cast(CustID as varchar(10))+'|'+CustName as CustIDName, CustName + ' '+isnull(CustTel,'') as CustNameTel from CRMCustomer" + OnlyDisplayActiveRecords + Filter + " order by CustNameTel")); }
public DataTable GetEmployeesByOfficeAndDay(string workOffice, string workDate) { DataTable dt = new DataTable(); dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select employeeNum from tblReservationTime where workOffice='" + workOffice + "' and workDate='" + workDate + "'"); return(dt); }
public object LoadById(Type type, string key, string value) { if (string.IsNullOrEmpty(value)) { return(null); } int iValue = 0; int.TryParse(value, out iValue); string sql = "select top 1 * from " + type.Name; sql += " where " + key + "=" + iValue; DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); //------------------------------ if (dt.Rows.Count == 0) { return(null); } else { var Entity = Activator.CreateInstance(type); DataRow dr = dt.Rows[0]; foreach (DataColumn dc in dt.Columns) { PropertyInfo pi = Entity.GetType().GetProperty(dc.ColumnName); if (pi != null) { if (dr[dc.ColumnName] != DBNull.Value) { object ColumnValue = dr[dc.ColumnName]; //类型“System.Decimal”的对象无法转换为类型“System.String” if (pi.PropertyType == typeof(string)) { ColumnValue = dr[dc.ColumnName].ToString(); } //类型“System.String”的对象无法转换为类型“System.Char” //set char(1) to string data type in dbml file if (pi.PropertyType == typeof(Char)) { ColumnValue = ColumnValue.ToString().ToCharArray()[0]; } pi.SetValue(Entity, ColumnValue, null); } else { pi.SetValue(Entity, null, null); } } } return(Entity); } }
public DataTable GetVisum(int Year, int Month) { string sql; sql = @"select * from vw_BillVisum where SUBSTRING(InnerReferenceID, 4, 2)='" + Year.ToString().Substring(2) + "' and SUBSTRING(InnerReferenceID, 6, 2)='" + Month.ToString("00") + "'"; DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
public DataTable GetEmailGroup(long?OwnerID) { if (OwnerID == null) { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select distinct GroupName from CRMEmailGroup")); } else { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select distinct GroupName from CRMEmailGroup where OwnerID=" + OwnerID.ToString())); } }
public DataTable getReservation(string strEmployeeNum, DateTime start, int days) { DataTable table = new DataTable(); string sql = "SELECT * FROM vw_Reservation"; sql += " WHERE NOT ((eventEnd <= '" + start.ToShortDateString() + @"') OR (eventStart >= '" + start.AddDays(days).ToShortDateString() + "'))"; sql += " and employeeNum='" + strEmployeeNum + "'"; table = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(table); }
public DataTable GetUserByDep() { string sql = @"SELECT UserName, CAST(UserID AS varchar(10)) AS userid, DepName FROM vw_CRMUser WHERE (UserName <> 'admin') AND (IsActive = 1) UNION SELECT ' ' + DepName AS username, 'optgroup' AS Userid, DepName FROM CRMDepartment ORDER BY DepName, UserName"; return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql)); }
//每月会计记录,现在不按bookingDate,按账单号分月 public DataTable GetTicketAccountingReport(int Year, int Month, int DepCode) { string sql; sql = @"select * from vw_BillTicketAccount where SUBSTRING(InnerReferenceID, 4, 2)='" + Year.ToString().Substring(2) + "' and SUBSTRING(InnerReferenceID, 6, 2)='" + Month.ToString("00") + "' and left(InnerReferenceID,2)=" + DepCode.ToString() + " order by BookingDate,InnerReferenceID"; DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
//按bookingdate,还是ApplyDate呢?--按账单号! public DataTable GetVisumAccount(int Year, int Month, string DepPrefix = "") { string sql; sql = @"select * from vw_BillVisumAccount where SUBSTRING(InnerReferenceID, 4, 2)='" + Year.ToString().Substring(2) + "' and SUBSTRING(InnerReferenceID, 6, 2)='" + Month.ToString("00") + "'"; if (DepPrefix != "") { sql += " and left(InnerReferenceID,2)='" + DepPrefix + "'"; } DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
public DataTable GetAuditLog(long custID, string CreateDate, string CreateUser, string CreateUserID) { string sql = @"select g.ActionLog, g.ActionBy, g.ActionAt,u.UserName from CRMAuditLog g inner join CRMUser u on g.ActionBy=u.UserID where PKId=" + custID.ToString() + " and Action='SaveCustomer' order by ActionAt"; DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); DataRow dr = dt.NewRow(); dr["ActionLog"] = "新建客户资料"; dr["ActionBy"] = CreateUserID; dr["ActionAt"] = CreateDate; dr["UserName"] = CreateUser; dt.Rows.InsertAt(dr, 0); return(dt); }
public DataTable SearchByCriteria(string TableName, string ColumnName, string Filter, string OrderBy) { string sql = "select " + ColumnName + " from " + TableName; if (string.IsNullOrEmpty(Filter) == false) { sql += " where 1=1 " + Filter; //.Replace("'", "''").Replace("\"", "'"); } if (string.IsNullOrEmpty(OrderBy) == false) { sql += " order by " + OrderBy; } DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
//每月出票记录,按天分开 public DataTable GetIssue(int Year, int Month, params int[] DepCode) { string sql; sql = @"select * from BillDailyIssue where year(IssueDate)=" + Year.ToString() + " and month(IssueDate)=" + Month.ToString(); if (DepCode.Length > 0) { sql += " and left(InnerReferenceID,2)=" + DepCode[0].ToString(); } sql += " order by IssueDate,InnerReferenceID"; DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
public DataTable GetTopN(Type type, int N, string Filter, string OrderBy) { string sql = "select top " + N + " * from " + type.Name; if (string.IsNullOrEmpty(Filter) == false) { sql += " where " + Filter.Replace("'", "''").Replace("\"", "'"); } if (string.IsNullOrEmpty(OrderBy) == false) { sql += " order by " + OrderBy; } DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); return(dt); }
//按市场部门找客户 public long[] GetCategoryRelativeCustomer(int catID) { //category对应的产品codes DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, @"select prodID from crmCategoryProd cp inner join crmproduct p on left(p.code,len(cp.prodcode)) = cp.prodcode where catid=" + catID.ToString()); long[] prodIDs = new long[dt.Rows.Count]; for (int i = 0; i < dt.Rows.Count; i++) { prodIDs[i] = Convert.ToInt32(dt.Rows[i][0]); } var qry = from t in CRMCustomerProds .Where(t => prodIDs.Contains(t.ProdID)) select t.CustID; return(qry.ToArray()); }
public bool checkConflict(long reservationID, string EmpNum, DateTime EventStart, DateTime EventEnd, ref string ErrMsg) { string sql = string.Empty; DataTable table = new DataTable(); sql = "select eventStart,eventEnd from vw_Reservation where employeeNum='" + EmpNum + "' and eventStart between '" + EventStart.Date.ToShortDateString() + "' and '" + EventStart.Date.AddDays(1).ToShortDateString() + "' and NOT(eventStart>='" + EventEnd + "' or eventEnd<='" + EventStart + "') and reservationID<>" + reservationID; table = DBExtBase.ExeFillTblBySqlText(this.dataCtx, sql); if (table.Rows.Count > 0) { ErrMsg = "預約時間有衝突,該員工這個時間段" + ((DateTime)table.Rows[0]["eventStart"]).ToShortTimeString() + "---" + ((DateTime)table.Rows[0]["eventEnd"]).ToShortTimeString() + "已被預約."; return(false); } return(true); }
public DataTable GetCustomer() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMCustomer" + OnlyDisplayActiveRecords)); }
public DataTable GetCountry() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select Country+'-'+ Remark as Country,CountryID from CRMCountry")); }
public DataTable GetRole() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMRole")); }
public DataTable GetRoleBelowGrade(int grade) { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMRole where RoleGrade<=" + grade.ToString())); }
public DataTable GetProduct() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMProduct")); }
public DataTable GetProductDDL() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select *, replicate('-',len(code)-3)+product as Proddl from CRMProduct where len(code)<=6 order by code")); }
public DataTable GetActionType() { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMActionType")); }
public DataTable GetCustomerByUserId(string UserID) { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMCustomer where CustOwnerID=" + UserID)); }
public DataTable GetEmailGroup(string GroupName, long OwnerID) { return(DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMEmailGroup where GroupName='" + GroupName + "' and OwnerID=" + OwnerID.ToString())); }
//每个员工的业绩 public DataTable GetMonthlyProfit(int Year) { DataTable dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "exec usp_GetMonthlyProfit " + Year.ToString()); return(dt); }