public DataTable getReservationLeave(string Date)
{
DataTable dt = new DataTable();
dt = DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from vw_ReservationLeave where workdate='" + Date + "'");
return(dt);
}
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 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"));
}
/// <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 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 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 void SetStatusActive(Type type, string key, string value)
{
string sql = "update " + type.Name;
sql += " set status='A' where " + key + "=" + value;
DBExtBase.ExeNonQueryBySqlText(this.dataCtx, sql);
}
public void SoftDeleteById(Type type, string key, string value)
{
string sql = "update " + type.Name;
sql += " set IsActive=0 where " + key + "=" + value;
DBExtBase.ExeNonQueryBySqlText(this.dataCtx, sql);
}
public void DeleteById(Type type, string key, string value)
{
string sql = "delete from " + type.Name;
sql += " where " + key + "=" + value;
DBExtBase.ExeNonQueryBySqlText(this.dataCtx, sql);
}
//log
public void WriteLog(LogInfo entity)
{
string sql = "INSERT INTO [tblLog] ([Operator],[OpDate],[EventDate],[Client],[Employee],[Tel],[LogInfo]) VALUES(";
sql += "N'" + entity.op + "','" + entity.opDate.ToString() + "','" + entity.eventDate.ToString();
sql += "',N'" + entity.client + "',N'" + entity.employee + "','" + entity.tel + "',N'" + entity.logInfo.Replace("'", "''") + "')";
DBExtBase.ExeNonQueryBySqlText(this.dataCtx, sql);
}
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 int GetTicketCountByMonth(int Year, int Month)
{
var TicketCnt = DBExtBase.ExeScalarBySqlText(this.dataCtx, @"
SELECT COUNT(FlightTicketNum) AS TicketCnt FROM vw_BillTicketReportDetail
WHERE (YEAR(BookingDate) = " + Year.ToString() + ") AND (MONTH(BookingDate) = "
+ Month.ToString() + ")");
return((int)TicketCnt);
}
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 GetUsersByGrade(int Grade)
//{
// //return DBExtBase.ExeFillTblBySqlText(this.dataCtx, "select * from CRMUser");
//}
public void TransferCustomer(string FromUser, string ToUser, bool DelFromUser)
{
string sql = "Update CRMCustomer set CustOwnerId=" + ToUser + " where CustOwnerID=" + FromUser;
if (DelFromUser)
{
sql += " Update CRMUser set isActive=0 wher UserID=" + FromUser;
}
DBExtBase.ExeNonQueryBySqlText(this.dataCtx, sql);
}
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()));
}
}
//每月会计记录,现在不按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);
}
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));
}
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 DateTime getReservationDateByMobile(string Mobile)
{
object dt = DBExtBase.ExeScalarBySqlText(this.dataCtx, "select eventstart from vw_Reservation where eventStart >= getdate()-1 and MobilePhone='" + Mobile.Replace("'", "''") + "'");
if (dt == null)
{
return(DateTime.Now);
}
else
{
return((DateTime)dt);
}
}
public DateTime getReservationDateByID(long ReservationID)
{
object dt = DBExtBase.ExeScalarBySqlText(this.dataCtx, "select eventstart from tblReservation where ReservationID=" + ReservationID.ToString());
if (dt == null)
{
return(DateTime.Now);
}
else
{
return((DateTime)dt);
}
}
/// <summary>
/// 是否下属的客户
/// </summary>
/// <param name="LoginUserID"></param>
/// <param name="CustOwnerID"></param>
/// <returns></returns>
public bool IsSubordinateCustomer(long LoginUserID, string CustOwnerID)
{
String sql = "select count(*) from dbo.GetSubordinateUser(" + LoginUserID + ") where userid=" + CustOwnerID;
if ((int)DBExtBase.ExeScalarBySqlText(dataCtx, sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
//按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 string GetNextInnerReferenceID(string DepName, string BookingDate)
{
string InnerReferenceID = "28";
if (DepName.Contains("Maastricht"))
{
InnerReferenceID = "68";
}
else if (DepName.Contains("Arnhem"))
{
InnerReferenceID = "68";
}
else if (DepName.Contains("Düsseldorf"))
{
InnerReferenceID = "51";
}
else if (DepName.Contains("Stuttgart"))
{
InnerReferenceID = "52";
}
else if (DepName.Contains("Köln"))
{
InnerReferenceID = "53";
}
else if (DepName.Contains("Nürnberg"))
{
InnerReferenceID = "55";
}
DateTime dtBookingDate = DateTime.Parse(BookingDate);
InnerReferenceID += "-" + dtBookingDate.Year.ToString().Substring(2) + dtBookingDate.Month.ToString("00");
string CurrentMax = DBExtBase.ExeScalarBySqlText(this.dataCtx,
@"select max(InnerReferenceID) from BillVisa
where InnerReferenceID like '" + InnerReferenceID + "%'").ToString();
if (string.IsNullOrEmpty(CurrentMax))
{
return(InnerReferenceID + "001");
}
else
{
return(InnerReferenceID + (int.Parse(CurrentMax.Substring(CurrentMax.Length - 3)) + 1).ToString("000"));
}
}
//没有银行对账记录,或者没有全部付款,都返回false
public bool IsPaid(object Id)
{
string sql;
sql = @"select count(ID) from BillTicketPerson
where TicketID=" + Id.ToString() + " and ( isnull(BankStatement,'')='' or PayNotEnough=1) ";
int cnt = (int)DBExtBase.ExeScalarBySqlText(this.dataCtx, sql);
if (cnt > 0)
{
return(false);
}
else
{
return(true);
}
}
//按市场部门找客户
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());
}