public static Sequence <AdminLog> LogByWeek <AdminLog>(DateTime weektime) { string weekname = _GetWeekName(weektime); var filename = weekname + ".log"; string FullFileName = System.IO.Path.Combine(LogFolder, filename); return(GlobalDatabase.GetSequence <AdminLog>(FullFileName) as Sequence <AdminLog>); }
static AdminLogService() { GlobalDatabase = Kooboo.Data.DB.Global(); LogFolder = System.IO.Path.Combine(Data.AppSettings.RootPath, "AppData", "AdminLog"); IOHelper.EnsureDirectoryExists(LogFolder); var setting = new Setting(); setting.AppendColumn("Path", typeof(string), 800); LastPath = GlobalDatabase.GetOrCreateTable("userpath", setting); }
static UserLoginService() { GlobalDatabase = Kooboo.Data.DB.Global(); var setting = new Setting(); setting.AppendColumn("Path", typeof(string), 800); LastPath = GlobalDatabase.GetOrCreateTable("userpath", setting); IgnorePath = new List <string>(); IgnorePath.Add("/_admin/account"); IgnorePath.Add("/_admin/scripts"); IgnorePath.Add("/_admin/styles"); IgnorePath.Add("/_admin/images"); IgnorePath.Add("/_admin/help"); IgnorePath.Add("_ddmin/sites/edit"); }
public object Calculate_AllocatePercent(dynamic data) { bool IsConnectSAP = false; int ResponseCode = 500; object ResponseData = null; string ErrrorMessage = string.Empty; ICON.SAP.API.SAPB1 SAPB1 = oSAPB1(); DateTime TranDate = DateTime.Now; DBHelper DBHelper = new DBHelper(ICON.Configuration.Database.REM_ConnectionString, null); SAP_Interface_Log Log = ICON.Interface.Transaction.CreateSAPLog("AP_REM", "Calculate_AllocatePercent", TranDate.ToString("yyyyMMdd_HHmmssfff"), "DATETIME", null, null, Newtonsoft.Json.JsonConvert.SerializeObject(data), TranBy); try { List <string> Errors = new List <string>(); int Year = 0; int Month = 0; string ProjectID = (string)data.project_id; Int32.TryParse((string)data.year, out Year); Int32.TryParse((string)data.month, out Month); //if (string.IsNullOrEmpty(ProjectID)) Errors.Add("missing value for parameter : 'project_id'"); if (Year.ToString() != (string)data.year) { Errors.Add("invalid format for parameter : 'year'"); } if (Month.ToString() != (string)data.month) { Errors.Add("invalid format for parameter : 'month'"); } if (Errors.Count > 0) { throw new Exception(string.Join("\n", Errors)); } DateTime NOW = DateTime.Today; DateTime SendDate = new DateTime(Year, Month, 1); int Start = ((SendDate.Year - NOW.Year) * 12) + SendDate.Month - NOW.Month; #region SQL System.Text.StringBuilder SQL = new System.Text.StringBuilder(); SQL.AppendLine($"DECLARE @Start INT = {Start}"); SQL.AppendLine($"DECLARE @End INT = {Start}"); SQL.AppendLine(""); SQL.AppendLine("CREATE TABLE #Temp ("); SQL.AppendLine(" [ProjectID] NVARCHAR(50)"); SQL.AppendLine(" ,[ProjectName] NVARCHAR(MAX)"); SQL.AppendLine(" ,[TotalArea] NUMERIC(18,2)"); SQL.AppendLine(" ,[CalculateArea] NUMERIC(18,2)"); SQL.AppendLine(" ,[Ratio] NUMERIC(18,2)"); SQL.AppendLine(" ,[CalculatePeriodM] INT"); SQL.AppendLine(" ,[CalculatePeriodY] INT"); SQL.AppendLine(" ,[CalculateDate] DATETIME"); SQL.AppendLine(" ,[UsePeriodM] INT"); SQL.AppendLine(" ,[UsePeriodY] INT"); SQL.AppendLine(" ,[TransferInPeriod] NUMERIC(18,2)"); SQL.AppendLine("CONSTRAINT [PK_Temp] PRIMARY KEY CLUSTERED "); SQL.AppendLine("("); SQL.AppendLine(" [ProjectID] ASC,"); SQL.AppendLine(" CalculateDate ASC"); SQL.AppendLine(")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"); SQL.AppendLine(") ON [PRIMARY]"); SQL.AppendLine(""); SQL.AppendLine(";WITH ROWYear AS ("); SQL.AppendLine(" SELECT DATEADD(MONTH,@Start - 1 ,DATEADD(DAY,1,EOMONTH(GETDATE()))) as n"); SQL.AppendLine(""); SQL.AppendLine(" UNION ALL"); SQL.AppendLine(""); SQL.AppendLine(" SELECT DATEADD(MONTH,1,n) FROM ROWYear WHERE n < DATEADD(MONTH,@End - 1,DATEADD(DAY,1,EOMONTH(GETDATE())))"); SQL.AppendLine(")"); SQL.AppendLine(""); SQL.AppendLine("INSERT INTO #Temp(ProjectID,ProjectName,CalculatePeriodM,CalculatePeriodY,CalculateDate,UsePeriodM,UsePeriodY)"); SQL.AppendLine("SELECT PJ.ProjectID,PJ.ProjectName, MONTH(R.n), YEAR(R.n), EOMONTH(R.n), MONTH(DATEADD(MONTH,1,R.n)), YEAR(DATEADD(MONTH,1,R.n))"); SQL.AppendLine("FROM ROWYear R, Sys_Master_Projects PJ"); SQL.AppendLine("WHERE 1=1"); if (!string.IsNullOrEmpty(ProjectID)) { SQL.AppendLine($" AND PJ.ProjectID = '{ProjectID}'"); } SQL.AppendLine("option (maxrecursion 0)"); SQL.AppendLine(""); SQL.AppendLine(""); SQL.AppendLine("UPDATE TMP SET TMP.TotalArea = PJ.TotalArea"); SQL.AppendLine("FROM #Temp TMP"); SQL.AppendLine("INNER JOIN "); SQL.AppendLine("("); SQL.AppendLine(" SELECT PJ.ProjectID"); SQL.AppendLine(" , SUM(UN.TitledeedArea) TotalArea"); SQL.AppendLine(" FROM"); SQL.AppendLine(" Sys_Master_Projects PJ"); SQL.AppendLine(" INNER JOIN Sys_Master_Units UN ON UN.ProjectID = PJ.ProjectID"); SQL.AppendLine(" WHERE"); SQL.AppendLine(" 1=1"); SQL.AppendLine(" AND ISNULL(UN.isDelete,0) = 0"); SQL.AppendLine(" GROUP BY PJ.ProjectID"); SQL.AppendLine(") PJ ON TMP.ProjectID = PJ.ProjectID"); SQL.AppendLine(""); SQL.AppendLine("--SELECT TEMP1.ProjectID, TEMP1.CalculateDate"); SQL.AppendLine("--, ISNULL(TEMP2.TitledeedArea,0) AS CalculateArea"); SQL.AppendLine("--, ISNULL(TEMP2.TitledeedArea,0) / TEMP1.TotalArea * 100 AS Ratio"); SQL.AppendLine("--, ISNULL(TEMP2.TitledeedArea,0) - ISNULL(TEMP2.TitledeedAreaOld,0) AS TransferInPeriod"); SQL.AppendLine("UPDATE TEMP1 SET"); SQL.AppendLine(" TEMP1.CalculateArea = ISNULL(TEMP2.TitledeedArea,0)"); SQL.AppendLine(" , Ratio = ISNULL(TEMP2.TitledeedArea,0) / TEMP1.TotalArea * 100"); SQL.AppendLine(" , TransferInPeriod = ISNULL(TEMP2.TitledeedArea,0) - ISNULL(TEMP2.TitledeedAreaOld,0)"); SQL.AppendLine("FROM "); SQL.AppendLine("#Temp TEMP1"); SQL.AppendLine("LEFT JOIN ("); SQL.AppendLine(" SELECT TMP.ProjectID, TMP.CalculateDate"); SQL.AppendLine(" , SUM(CASE WHEN TMP.CalculateDate >= ISNULL(CO.TransferDate,CO.ContractDate) THEN UN.TitledeedArea ELSE 0 END) TitledeedArea"); SQL.AppendLine(" , SUM(CASE WHEN EOMONTH(DATEADD(MONTH,-1,TMP.CalculateDate)) >= ISNULL(CO.TransferDate,CO.ContractDate) THEN UN.TitledeedArea ELSE 0 END) TitledeedAreaOld"); SQL.AppendLine(" FROM #Temp TMP"); SQL.AppendLine(" INNER JOIN Sys_REM_Contracts CO ON TMP.ProjectID = CO.ProjectID "); SQL.AppendLine(" INNER JOIN Sys_Master_Units UN ON TMP.ProjectID = UN.ProjectID "); SQL.AppendLine(" WHERE "); SQL.AppendLine(" 1=1"); SQL.AppendLine(" AND ISNULL(CO.isTmp,0) = 0"); SQL.AppendLine(" AND CO.ContractID IS NOT NULL"); SQL.AppendLine(" AND UN.UnitID IS NOT NULL"); SQL.AppendLine(" AND CO.SBUID = 'PM001'"); SQL.AppendLine(" AND CO.UnitID = UN.UnitID"); SQL.AppendLine(" AND ISNULL(UN.isDelete,0) = 0"); SQL.AppendLine(" AND ISNULL(CO.SaleOrderStatus,'') NOT IN ('C')"); SQL.AppendLine(" AND TMP.CalculateDate >= ISNULL(CO.TransferDate,CO.ContractDate)"); SQL.AppendLine(" GROUP BY TMP.ProjectID, TMP.CalculateDate"); SQL.AppendLine(") TEMP2 ON TEMP1.ProjectID = TEMP2.ProjectID AND TEMP1.CalculateDate = TEMP2.CalculateDate"); SQL.AppendLine(""); SQL.AppendLine("UPDATE TMP SET TMP.Ratio = 100"); SQL.AppendLine("FROM #Temp TMP"); SQL.AppendLine("INNER JOIN Sys_Master_Projects PJ ON TMP.ProjectID = PJ.ProjectID AND ISNULL(PJ.ProjectType,'') = 'C'"); SQL.AppendLine("WHERE 1=1"); SQL.AppendLine(" AND ISNULL(PJ.JuristicStatus,'') = 'A'"); SQL.AppendLine(""); SQL.AppendLine("SELECT [ProjectID] AS project_id"); SQL.AppendLine(" ,[ProjectName] AS project_name"); SQL.AppendLine(" ,[TotalArea] AS total_area"); SQL.AppendLine(" ,[CalculateArea] AS calculate_area"); SQL.AppendLine(" ,[Ratio] AS ratio"); SQL.AppendLine(" ,[CalculatePeriodM] AS calculate_period_m"); SQL.AppendLine(" ,[CalculatePeriodY] AS calculate_period_y"); SQL.AppendLine(" ,[UsePeriodM] AS use_period_m"); SQL.AppendLine(" ,[UsePeriodY] AS use_period_y"); SQL.AppendLine(" ,[TransferInPeriod] AS transfer_in_period"); SQL.AppendLine("FROM #Temp"); SQL.AppendLine("ORDER BY"); SQL.AppendLine(" ProjectID, CalculateDate"); SQL.AppendLine(""); SQL.AppendLine("DROP TABLE #Temp"); #endregion List <Dictionary <string, object> > AllocateList = GlobalDatabase.LoadDictByQuery(DBHelper, SQL.ToString()); List <string> ProjectList = AllocateList.GroupBy(x => x["project_id"].ToString()).Select(x => x.Key).ToList(); System.Text.StringBuilder SQL_Company = new System.Text.StringBuilder(); SQL_Company.AppendLine("SELECT"); SQL_Company.AppendLine(" C.CompanyID AS CompanyID"); SQL_Company.AppendLine(" , PJ.ProjectID AS ProjectID"); SQL_Company.AppendLine(" , ISNULL(C.Value, '') AS DBName"); SQL_Company.AppendLine("FROM"); SQL_Company.AppendLine(" Sys_Conf_RealEstate C"); SQL_Company.AppendLine(" LEFT JOIN Sys_Master_Projects PJ ON C.CompanyID = PJ.CompanyID"); SQL_Company.AppendLine("WHERE 1 = 1"); SQL_Company.AppendLine(" AND C.CompanyID IS NOT NULL"); SQL_Company.AppendLine(" AND C.CompanyID <> '0'"); SQL_Company.AppendLine(" AND C.KEYNAME = 'DBName'"); SQL_Company.AppendLine($" AND PJ.ProjectID IN ('{string.Join("','", ProjectList)}')"); List <Dictionary <string, object> > CompanyConfigList = GlobalDatabase.LoadDictByQuery(DBHelper, SQL_Company.ToString()); List <string> CompanyList = CompanyConfigList.GroupBy(x => x["CompanyID"].ToString()).Select(x => x.Key).ToList(); foreach (string Company in CompanyList) { Dictionary <string, object> CompanyConfig = CompanyConfigList.Find(x => x["CompanyID"].ToString() == Company); if (CompanyConfig == null || CompanyConfig["DBName"].ToString() == "") { bool IsLock = true; foreach (Dictionary <string, object> Item in CompanyConfigList.FindAll(x => x["CompanyID"].ToString() == Company)) { Item["IsLock"] = IsLock; } } else { string DBName = CompanyConfig["DBName"].ToString(); SAPB1 = oSAPB1(DBName); SAPB1.ConnectCompanyDB(); IsConnectSAP = true; bool IsLock = SAPB1.GetIsLock(SendDate); SAPB1.DisConnectCompanyDB(); IsConnectSAP = false; foreach (Dictionary <string, object> Item in CompanyConfigList.FindAll(x => x["CompanyID"].ToString() == Company)) { Item["IsLock"] = IsLock; } } } foreach (Dictionary <string, object> Allocate in AllocateList) { string Project = Allocate["project_id"].ToString(); Dictionary <string, object> CompanyConfig = CompanyConfigList.Find(x => x["ProjectID"].ToString() == Project); if (CompanyConfig == null) { Allocate["is_lock"] = false; } else { Allocate["is_lock"] = Convert.ToBoolean(CompanyConfig["IsLock"]); } } object resp = new { year = Year, month = Month, allocate_percent = AllocateList, }; ResponseCode = 200; ResponseData = new { status = true, message = "success", data = resp }; } catch (Exception ex) { ErrrorMessage = ex.Message; ResponseData = new { status = false, message = ex.Message }; } finally { ICON.Interface.Transaction.UpdateSAPLog(Log.TranID, Newtonsoft.Json.JsonConvert.SerializeObject(ResponseData), ResponseCode, ErrrorMessage); if (IsConnectSAP) { SAPB1.DisConnectCompanyDB(); } } return(ResponseData); }
public ToolkitData(ModContentPack content) : base(content) { globalDatabase = GetSettings <GlobalDatabase>(); }
public static void SetDatabase(GlobalDatabase data) { database = data; }