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);
}
