public static string CreateNewCustomerID() { List <SqlParameter> sp = new List <SqlParameter>() { }; string query = "select top 1 CustomerID from Customer order by CustomerID desc"; DataTable dt = DataFacade.DTSQLCommand(query, sp); string lastID = ""; foreach (DataRow row in dt.Rows) { lastID = row["CustomerID"].ToString(); } string[] stringArr = lastID.Split('-'); int part3 = Convert.ToInt32(stringArr[2]); string newpart = (part3 + 1).ToString(); return(stringArr[0] + "-" + stringArr[1] + "-" + newpart); }
public static List <Model.mdlLogLocationReq> getLog_LocationReq(string lDate) { var mdlLogLocationReqList = new List <Model.mdlLogLocationReq>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@Date", SqlDbType = SqlDbType.NVarChar, Value = lDate }, }; DataTable dtLog = DataFacade.DTSQLCommand(@"SELECT TOP 1 Date,RequestCounter From Log_LocationRequest WHERE Date = @Date", sp); foreach (DataRow row in dtLog.Rows) { var mdlLogLocationReq = new Model.mdlLogLocationReq(); mdlLogLocationReq.Date = row["Date"].ToString(); mdlLogLocationReq.RequestCounter = row["RequestCounter"].ToString(); mdlLogLocationReqList.Add(mdlLogLocationReq); } return(mdlLogLocationReqList); }
public static List <Model.Menu> GetAccessMobileMenu(string lRoleID, string lIsAccess) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.NVarChar, Value = lRoleID }, new SqlParameter() { ParameterName = "@IsAccess", SqlDbType = SqlDbType.NVarChar, Value = Convert.ToBoolean(lIsAccess) }, }; DataTable dtAccess = DataFacade.DTSQLCommand(@"SELECT a.RoleID,b.ModuleID,b.ModuleType FROM AccessRoleMobile a INNER JOIN MenuMobile b ON a.MenuID = b.ModuleID WHERE a.RoleID = @RoleID AND IsActive = 1", sp); var mdlMenuList = new List <Model.Menu>(); foreach (DataRow row in dtAccess.Rows) { var mdlMenu = new Model.Menu(); mdlMenu.MenuID = row["ModuleID"].ToString(); mdlMenu.MenuName = row["ModuleID"].ToString(); mdlMenuList.Add(mdlMenu); } return(mdlMenuList); }
public static List <Model.Menu> GetAccessMenu(string lRoleID, string lIsAccess) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.NVarChar, Value = lRoleID }, new SqlParameter() { ParameterName = "@IsAccess", SqlDbType = SqlDbType.NVarChar, Value = Convert.ToBoolean(lIsAccess) }, }; DataTable dtAccess = DataFacade.DTSQLCommand(@"SELECT MenuID,MenuName FROM Menu WHERE MenuID IN (SELECT MenuID FROM AccessRole WHERE RoleID = @RoleID AND IsAccess = @IsAccess AND MenuID LIKE 'M%')", sp); var mdlMenuList = new List <Model.Menu>(); foreach (DataRow row in dtAccess.Rows) { var mdlMenu = new Model.Menu(); mdlMenu.MenuID = row["MenuID"].ToString(); mdlMenu.MenuName = row["MenuName"].ToString(); mdlMenuList.Add(mdlMenu); } return(mdlMenuList); }
public static List <Model.mdlAnswer> LoadAnswer() { var listAnswer = new List <Model.mdlAnswer>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT AnswerID, AnswerText, QuestionID, SubQuestion, IsSubQuestion, Sequence, No, IsActive FROM Answer"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlAnswer(); model.AnswerID = row["AnswerID"].ToString(); model.AnswerText = row["AnswerText"].ToString(); model.QuestionID = row["QuestionID"].ToString(); model.SubQuestion = Convert.ToBoolean(row["SubQuestion"].ToString()); model.IsSubQuestion = Convert.ToBoolean(row["IsSubQuestion"].ToString()); model.Sequence = Convert.ToInt32(row["Sequence"].ToString()); model.No = row["No"].ToString(); model.IsActive = Convert.ToBoolean(row["IsActive"].ToString()); listAnswer.Add(model); } return(listAnswer); }
public static Model.mdlDeliveryOrder LoadDeliveryOrderbyDONumber(string lDoNumber) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@DoNumber", SqlDbType = SqlDbType.NVarChar, Value = lDoNumber } }; DataTable dtDeliveryOrder = DataFacade.DTSQLCommand(@"SELECT TOP 1 DONumber ,DODate ,BranchID ,VehicleID From DeliveryOrder WHERE DONumber = @DoNumber", sp); var mdlDeliveryOrder = new Model.mdlDeliveryOrder(); foreach (DataRow row in dtDeliveryOrder.Rows) { mdlDeliveryOrder.DONumber = row["DONumber"].ToString(); mdlDeliveryOrder.DODate = Convert.ToDateTime(row["DODate"]).ToString("yyyy-MM-dd hh:mm:ss"); mdlDeliveryOrder.BranchID = row["BranchID"].ToString(); mdlDeliveryOrder.VehicleID = row["VehicleID"].ToString(); } return(mdlDeliveryOrder); }
public static Model.mdlIdleCounter CheckIfIdleCounterExist(string employeeID, string branchID) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@employeeID", SqlDbType = SqlDbType.NVarChar, Value = employeeID }, new SqlParameter() { ParameterName = "@branchID", SqlDbType = SqlDbType.NVarChar, Value = branchID } }; DataTable dt = DataFacade.DTSQLCommand("SELECT EmployeeID,BranchID,StartDate,Longitude,Latitude,Counter,StartDate FROM IdleCounter WHERE EmployeeID = @employeeID AND BranchID = @branchID", sp); var mdlIdleCounter = new Model.mdlIdleCounter(); foreach (DataRow row in dt.Rows) { mdlIdleCounter.EmployeeID = row["EmployeeID"].ToString(); mdlIdleCounter.BranchID = row["BranchID"].ToString(); mdlIdleCounter.Longitude = row["Longitude"].ToString(); mdlIdleCounter.Latitude = row["Latitude"].ToString(); mdlIdleCounter.Counter = Convert.ToInt32(row["Counter"]); mdlIdleCounter.StartDate = row["StartDate"].ToString(); } return(mdlIdleCounter); //jika false tidak ada data, jika true ada data }
//--------------------------------Facade Menu-----------------------------------------------// public static List <Model.Menu> GetSearchMenu(string lRoleID, string lType) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.NVarChar, Value = lRoleID }, new SqlParameter() { ParameterName = "@Type", SqlDbType = SqlDbType.NVarChar, Value = lType }, }; DataTable dtMenu = DataFacade.DTSQLCommand("SELECT a.MenuUrl,a.MenuName FROM menu a INNER JOIN AccessRole b ON a.MenuID = b.MenuID WHERE b.RoleID = @RoleID AND b.IsAccess = 1 AND a.Type = @Type;", sp); var mdlMenuList = new List <Model.Menu>(); foreach (DataRow row in dtMenu.Rows) { var mdlMenu = new Model.Menu(); mdlMenu.MenuUrl = row["MenuUrl"].ToString(); mdlMenu.MenuName = row["MenuName"].ToString(); mdlMenuList.Add(mdlMenu); } return(mdlMenuList); }
public static List <Model.mdlSubMenu> GetEditableMenu(string lRoleID, string lIsModify) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.NVarChar, Value = lRoleID }, new SqlParameter() { ParameterName = "@IsModify", SqlDbType = SqlDbType.Bit, Value = lIsModify }, }; DataTable dtMenu = DataFacade.DTSQLCommand(@"SELECT MenuID,MenuName FROM Menu where MenuID in (select menuid from accessrole where RoleID=@RoleID AND IsModify=@IsModify)", sp); var mdlMenuList = new List <Model.mdlSubMenu>(); foreach (DataRow row in dtMenu.Rows) { var mdlMenu = new Model.mdlSubMenu(); mdlMenu.menu = row["MenuID"].ToString(); mdlMenu.name = row["MenuName"].ToString(); mdlMenuList.Add(mdlMenu); } return(mdlMenuList); }
public static List <Model.mdlDailyMsg> LoadDailyMessage2(Model.mdlParam json) { var mdlDailyMsgList = new List <Model.mdlDailyMsg>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date }, //new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date.AddDays(1) }, new SqlParameter() { ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID }, new SqlParameter() { ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID } }; DataTable dtDailyMsg = DataFacade.DTSQLCommand(@" SELECT a.MessageID, a.MessageName, a.MessageDesc, CONCAT('//img//DailyMessage//', c.ImageBase64) as MessageImg, a.CreatedBy, a.Date FROM ( SELECT DISTINCT a.MessageID FROM DailyMessage a INNER JOIN DailyMessageDetail b ON a.MessageID = b.MessageID WHERE a.Date <= @Date AND a.EndDate >= @Date AND b.BranchID = @BranchID AND b.EmployeeID = @EmployeeID ) AS qr INNER JOIN DailyMessage a ON a.MessageID = qr.MessageID LEFT JOIN CustomerImage c ON c.ImageID = a.MessageImg", sp); foreach (DataRow row in dtDailyMsg.Rows) { var mdlDailyMsg = new Model.mdlDailyMsg(); mdlDailyMsg.MessageID = row["MessageID"].ToString(); mdlDailyMsg.MessageName = row["MessageName"].ToString(); mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString(); mdlDailyMsg.MessageImg = row["MessageImg"].ToString(); mdlDailyMsg.BranchID = ""; mdlDailyMsg.EndDate = ""; mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd hh:mm:ss"); mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString(); mdlDailyMsgList.Add(mdlDailyMsg); } return(mdlDailyMsgList); }
public static Core.Model.mdlSetDeviceID SetUserConfig(Core.Model.mdlSetDeviceIDParam param) { if (param.Uid == null) { param.Uid = ""; } DataTable dt = new DataTable(); List<SqlParameter> sp = new List<SqlParameter>() { new SqlParameter() {ParameterName = "@Uid", SqlDbType = SqlDbType.NVarChar, Value = param.Uid }, new SqlParameter() {ParameterName = "@deviceID", SqlDbType = SqlDbType.NVarChar, Value = param.deviceID }, }; dt = DataFacade.DTSQLCommand(@"SELECT * FROM UserConfig WHERE Uid = @Uid OR deviceID = @deviceID", sp); var mdlUserConfig = new Model.mdlSetDeviceID(); foreach (DataRow row in dt.Rows) { mdlUserConfig.EmployeeID = row["EmployeeID"].ToString(); mdlUserConfig.BranchID = row["BranchID"].ToString(); mdlUserConfig.BranchName = row["BranchName"].ToString(); mdlUserConfig.VehicleNumber = row["VehicleNumber"].ToString(); mdlUserConfig.IpLocal = row["IpLocal"].ToString(); mdlUserConfig.PortLocal = row["PortLocal"].ToString(); mdlUserConfig.IpPublic = row["IpPublic"].ToString(); mdlUserConfig.PortPublic = row["PortPublic"].ToString(); mdlUserConfig.IpAlternative = row["IpAlternative"].ToString(); mdlUserConfig.PortAlternative = row["PortAlternative"].ToString(); mdlUserConfig.Password = row["Password"].ToString(); mdlUserConfig.Uid = row["Uid"].ToString(); } if (mdlUserConfig.EmployeeID == "" | mdlUserConfig.EmployeeID == null) { mdlUserConfig.Result = "0"; mdlUserConfig.Role = ""; } else { mdlUserConfig.Result = "1"; mdlUserConfig.Role = getUserRole(mdlUserConfig.EmployeeID); } return mdlUserConfig; }
public static List <Model.mdlDailyMsg> GetSearchbySomeBranch(string keyword, DateTime keyworddate, string branchid) { branchid = StringFacade.NormalizedBranch(branchid); var mdlDailyMsgList = new List <Model.mdlDailyMsg>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ValidDate", SqlDbType = SqlDbType.Date, Value = keyworddate }, new SqlParameter() { ParameterName = "@Keyword", SqlDbType = SqlDbType.NVarChar, Value = "%" + keyword + "%" }, new SqlParameter() { ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = branchid } }; DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT distinct(a.MessageID) ,a.MessageName ,a.Date ,a.EndDate ,a.CreatedBy From DailyMessage a LEFT JOIN DailyMessageDetail b on b.MessageID = a.MessageID WHERE (a.Date <= @ValidDate AND a.EndDate >= @ValidDate) AND (a.MessageID like @Keyword OR MessageName like @Keyword) AND a.MessageID IN (SELECT MessageID from DailyMessageDetail where BranchID IN (" + branchid + "))", sp); foreach (DataRow row in dtDailyMsg.Rows) { var mdlDailyMsg = new Model.mdlDailyMsg(); mdlDailyMsg.MessageID = row["MessageID"].ToString(); mdlDailyMsg.MessageName = row["MessageName"].ToString(); mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("dd/MM/yyyy"); mdlDailyMsg.EndDate = Convert.ToDateTime(row["EndDate"]).ToString("dd/MM/yyyy"); mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString(); mdlDailyMsgList.Add(mdlDailyMsg); } return(mdlDailyMsgList); }
public static List <Model.Role> GetddlRole() { List <SqlParameter> sp = new List <SqlParameter>() { }; DataTable dtRole = DataFacade.DTSQLCommand("SELECT * FROM Role", sp); var mdlRoleList = new List <Model.Role>(); foreach (DataRow row in dtRole.Rows) { var mdlRole = new Model.Role(); mdlRole.RoleID = row["RoleID"].ToString(); mdlRole.RoleName = row["RoleName"].ToString(); mdlRoleList.Add(mdlRole); } return(mdlRoleList); }
public static List <Model.mdlSubMenu> GetSubMenu(string menuID) { List <SqlParameter> sp = new List <SqlParameter>() { }; DataTable dtSubMenu = DataFacade.DTSQLCommand("SELECT MenuID,MenuName FROM Menu where Type in (select type from menu where menuid = '" + menuID + "') and MenuID <> '" + menuID + "' ", sp); var mdlSubMenuList = new List <Model.mdlSubMenu>(); foreach (DataRow row in dtSubMenu.Rows) { var mdlSubMenu = new Model.mdlSubMenu(); mdlSubMenu.menu = row["MenuID"].ToString(); mdlSubMenu.name = " - " + row["MenuName"].ToString(); mdlSubMenuList.Add(mdlSubMenu); } return(mdlSubMenuList); }
public static List <Model.Menu> GetMenu() { List <SqlParameter> sp = new List <SqlParameter>() { }; DataTable dtMenu = DataFacade.DTSQLCommand("SELECT MenuID,MenuName FROM Menu where MenuID LIKE 'M%'", sp); var mdlMenuList = new List <Model.Menu>(); foreach (DataRow row in dtMenu.Rows) { var mdlMenu = new Model.Menu(); mdlMenu.MenuID = row["MenuID"].ToString(); mdlMenu.MenuName = row["MenuName"].ToString(); mdlMenuList.Add(mdlMenu); } return(mdlMenuList); }
public static List <Model.mdlCompetitorProduct> LoadCompetitorProduct(Model.mdlParam param, List <Model.mdlCompetitor> listCompetitor) { List <SqlParameter> sp = new List <SqlParameter>(); StringBuilder sb = new StringBuilder(); int count = 1; foreach (var com in listCompetitor) { var sqlParameter = new SqlParameter(); sqlParameter.ParameterName = "@ComID" + count.ToString(); if (com == listCompetitor.Last()) { sb.Append("@ComID" + count.ToString()); } else { sb.Append("@ComID" + count.ToString() + ","); } sqlParameter.SqlDbType = SqlDbType.NVarChar; sqlParameter.Value = com.CompetitorID; sp.Add(sqlParameter); count++; } var listCompetitorProduct = new List <Model.mdlCompetitorProduct>(); string sql = @"SELECT CompetitorID,CompetitorProductID,CompetitorProductName FROM CompetitorProduct WHERE CompetitorID IN (" + sb.ToString() + ")"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlCompetitorProduct(); model.CompetitorID = row["CompetitorID"].ToString(); model.CompetitorProductID = row["CompetitorProductID"].ToString(); model.CompetitorProductName = row["CompetitorProductName"].ToString(); listCompetitorProduct.Add(model); } return(listCompetitorProduct); }
public static List <Model.mdlCompetitor> LoadCompetitor(Model.mdlParam param) { var listCompetitor = new List <Model.mdlCompetitor>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT CompetitorID,CompetitorName FROM Competitor"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlCompetitor(); model.CompetitorID = row["CompetitorID"].ToString(); model.CompetitorName = row["CompetitorName"].ToString(); listCompetitor.Add(model); } return(listCompetitor); }
public static List <Model.mdlSubMenu> GetAccessSubMenu(string lRoleID, string menuID) { List <SqlParameter> sp = new List <SqlParameter>() { }; DataTable dtSubMenu = DataFacade.DTSQLCommand(@"SELECT MenuID,MenuName FROM Menu where Type in (select Type from Menu where menuid = '" + menuID + "') and MenuID <> '" + menuID + @"' AND MenuID IN (SELECT MenuID from AccessRole where IsAccess = 'true' and RoleID = '" + lRoleID + "')", sp); var mdlSubMenuList = new List <Model.mdlSubMenu>(); foreach (DataRow row in dtSubMenu.Rows) { var mdlSubMenu = new Model.mdlSubMenu(); mdlSubMenu.menu = row["MenuID"].ToString(); mdlSubMenu.name = " - " + row["MenuName"].ToString(); mdlSubMenuList.Add(mdlSubMenu); } return(mdlSubMenuList); }
public static List <Model.mdlDepositType> LoadDepositType() { var listDepositType = new List <Model.mdlDepositType>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT DepositTypeID, DepositTypeName FROM DepositType"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlDepositType(); model.DepositTypeID = row["DepositTypeID"].ToString(); model.DepositTypeName = row["DepositTypeName"].ToString(); listDepositType.Add(model); } return(listDepositType); }
public static List <Model.mdlDailyMsgRole> GetDataBySomeBranch(string keyword, string keyword2, string branchid) { var mdlDailyMsgRoleList = new List <Model.mdlDailyMsgRole>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@MessageID", SqlDbType = SqlDbType.NVarChar, Value = keyword }, new SqlParameter() { ParameterName = "@Keyword", SqlDbType = SqlDbType.NVarChar, Value = "%" + keyword2 + "%" }, new SqlParameter() { ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = "%" + branchid + "%" } }; DataTable dtDailyMsgDetail = DataFacade.DTSQLCommand(@"SELECT a.MessageID ,a.EmployeeID ,b.EmployeeName ,a.BranchID From DailyMessageDetail a INNER JOIN Employee b ON b.EmployeeID = a.EmployeeID WHERE (a.MessageID = @MessageID) AND (a.EmployeeID LIKE @Keyword OR b.EmployeeName LIKE @Keyword) AND a.BranchID IN (" + branchid + ") AND a.EmployeeID <> '' ORDER BY BranchID", sp); foreach (DataRow row in dtDailyMsgDetail.Rows) { var mdlDailyMsgRole = new Model.mdlDailyMsgRole(); mdlDailyMsgRole.MessageID = row["MessageID"].ToString(); mdlDailyMsgRole.EmployeeID = row["EmployeeID"].ToString(); mdlDailyMsgRole.BranchID = row["BranchID"].ToString(); mdlDailyMsgRole.EmployeeName = row["EmployeeName"].ToString(); mdlDailyMsgRoleList.Add(mdlDailyMsgRole); } return(mdlDailyMsgRoleList); }
public static List <Model.mdlTrackingJourney> GetTrackingCoordinate(string employeeID, string branchID, DateTime date) { List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@DateStart", SqlDbType = SqlDbType.DateTime, Value = date }, new SqlParameter() { ParameterName = "@DateFinish", SqlDbType = SqlDbType.DateTime, Value = date.AddDays(1) }, //new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date.AddDays(1) }, new SqlParameter() { ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = employeeID }, new SqlParameter() { ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = branchID } }; DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT TrackingDate,VehicleID,Latitude,Longitude FROM LiveTracking WHERE TrackingDate >= @DateStart AND TrackingDate < @DateFinish AND EmployeeID=@EmployeeID AND BranchID = @BranchID Order by TrackingDate", sp); var mdlTrackingList = new List <Model.mdlTrackingJourney>(); foreach (DataRow row in dtDailyMsg.Rows) { var mdlTracking = new Model.mdlTrackingJourney(); mdlTracking.vehicleID = row["VehicleID"].ToString(); mdlTracking.time = Convert.ToDateTime(row["TrackingDate"]).ToString("H:mm:ss"); mdlTracking.latitude = row["Latitude"].ToString(); mdlTracking.longitude = row["Longitude"].ToString(); mdlTrackingList.Add(mdlTracking); } return(mdlTrackingList); }
public static List <Model.mdlAnswer_Type> LoadAnswerType() { var listAnswerType = new List <Model.mdlAnswer_Type>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT AnswerTypeID, AnswerTypeText FROM AnswerType"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlAnswer_Type(); model.AnswerTypeID = row["AnswerTypeID"].ToString(); model.AnswerTypeText = row["AnswerTypeText"].ToString(); listAnswerType.Add(model); } return(listAnswerType); }
public static List <Model.mdlCompetitorActivity> LoadCompetitorActivity(Model.mdlParam param) { var listCompetitorActivity = new List <Model.mdlCompetitorActivity>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT [ActivityID],[CompetitorID],[ActivityName] FROM [CompetitorActivity]"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlCompetitorActivity(); model.ActivityID = row["ActivityID"].ToString(); model.CompetitorID = row["CompetitorID"].ToString(); model.ActivityName = row["ActivityName"].ToString(); listCompetitorActivity.Add(model); } return(listCompetitorActivity); }
public static List <Model.mdlQuestion_Set> LoadQuestionSet() { var listQuestionSet = new List <Model.mdlQuestion_Set>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT QuestionSetID, QuestionSetText FROM Question_Set WHERE IsActive=1"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlQuestion_Set(); model.QuestionSetID = row["QuestionSetID"].ToString(); model.QuestionSetText = row["QuestionSetText"].ToString(); listQuestionSet.Add(model); } return(listQuestionSet); }
public static List <Model.mdlQuestion_Category> LoadQuestionCategory() { var listQuestionCategory = new List <Model.mdlQuestion_Category>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT QuestionCategoryID, QuestionCategoryText FROM Question_Category"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlQuestion_Category(); model.QuestionCategoryID = row["QuestionCategoryID"].ToString(); model.QuestionCategoryText = row["QuestionCategoryText"].ToString(); listQuestionCategory.Add(model); } return(listQuestionCategory); }
public static List <Model.mdlQuestion> LoadQuestion(Model.mdlParam json) { var listQuestion = new List <Model.mdlQuestion>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.VarChar, Value = json.Role } }; string sql = @"SELECT a.QuestionID, a.QuestionText, a.AnswerTypeID, a.IsSubQuestion, a.Sequence, a.QuestionSetID, a.QuestionCategoryID, a.AnswerID, a.No, a.Mandatory, a.IsActive FROM Question a INNER JOIN Question_Role b ON b.QuestionID = a.QuestionID WHERE b.RoleID = @RoleID"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlQuestion(); model.QuestionID = row["QuestionID"].ToString(); model.QuestionText = row["QuestionText"].ToString(); model.AnswerTypeID = row["AnswerTypeID"].ToString(); model.IsSubQuestion = Convert.ToBoolean(row["IsSubQuestion"].ToString()); model.Sequence = Convert.ToInt32(row["Sequence"].ToString()); model.QuestionSetID = row["QuestionSetID"].ToString(); model.QuestionCategoryID = row["QuestionCategoryID"].ToString(); model.AnswerID = row["AnswerID"].ToString(); model.No = row["No"].ToString(); model.Mandatory = Convert.ToBoolean(row["Mandatory"].ToString()); model.IsActive = Convert.ToBoolean(row["IsActive"].ToString()); listQuestion.Add(model); } return(listQuestion); }
public static List <Model.mdlDailyMsg> LoadDailyMessage(Model.mdlParam json) { var mdlDailyMsgList = new List <Model.mdlDailyMsg>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date }, new SqlParameter() { ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID } }; DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT MessageID ,MessageName ,MessageDesc ,MessageImg ,CreatedBy ,Date From DailyMessage WHERE Date <= @Date AND EndDate >= @Date AND BranchID = @BranchID", sp); foreach (DataRow row in dtDailyMsg.Rows) { var mdlDailyMsg = new Model.mdlDailyMsg(); mdlDailyMsg.MessageID = row["MessageID"].ToString(); mdlDailyMsg.MessageName = row["MessageName"].ToString(); mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString(); mdlDailyMsg.MessageImg = row["MessageImg"].ToString(); mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd"); mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString(); mdlDailyMsgList.Add(mdlDailyMsg); } return(mdlDailyMsgList); }
public static List <Model.mdlSettings> GetCurrentSettings(string branchID) { var listSettings = new List <Model.mdlSettings>(); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@branchID", SqlDbType = SqlDbType.NVarChar, Value = branchID } }; DataTable dtSettings = DataFacade.DTSQLCommand("SELECT Name,Value FROM Settings WHERE BranchID = @branchID", sp); foreach (DataRow row in dtSettings.Rows) { var mdlSetting = new Model.mdlSettings(); mdlSetting.name = row["Name"].ToString(); mdlSetting.value = row["Value"].ToString(); listSettings.Add(mdlSetting); } return(listSettings); }
public static List <Model.mdlCompetitorActivityType> LoadCompetitorActivityType(Model.mdlParam param) { var listCompetitorActivityType = new List <Model.mdlCompetitorActivityType>(); List <SqlParameter> sp = new List <SqlParameter>() { }; string sql = "SELECT CompetitorActivityTypeID, Description, Category, Seq FROM CompetitorActivityType"; DataTable dt = DataFacade.DTSQLCommand(sql, sp); foreach (DataRow row in dt.Rows) { var model = new Model.mdlCompetitorActivityType(); model.CompetitorActivityTypeID = row["CompetitorActivityTypeID"].ToString(); model.Description = row["Description"].ToString(); model.Category = row["Category"].ToString(); model.Seq = row["Seq"].ToString(); listCompetitorActivityType.Add(model); } return(listCompetitorActivityType); }
public static Model.mdlResultList UploadDeposit(List <Model.mdlDepositParam> lParamlist) { var mdlResultList = new List <Model.mdlResult>(); foreach (var lParam in lParamlist) { var mdlResult = new Model.mdlResult(); List <SqlParameter> sp = new List <SqlParameter>() { //header new SqlParameter() { ParameterName = "@DepositID", SqlDbType = SqlDbType.NVarChar, Value = lParam.DepositID }, new SqlParameter() { ParameterName = "@VisitID", SqlDbType = SqlDbType.NVarChar, Value = lParam.VisitID }, new SqlParameter() { ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = lParam.EmployeeID }, new SqlParameter() { ParameterName = "@Status", SqlDbType = SqlDbType.NVarChar, Value = lParam.Status }, new SqlParameter() { ParameterName = "@ReceivedDate", SqlDbType = SqlDbType.NVarChar, Value = lParam.ReceivedDate }, new SqlParameter() { ParameterName = "@Description", SqlDbType = SqlDbType.NVarChar, Value = lParam.Description }, new SqlParameter() { ParameterName = "@CustomerID", SqlDbType = SqlDbType.NVarChar, Value = lParam.CustomerID }, new SqlParameter() { ParameterName = "@CreatedBy", SqlDbType = SqlDbType.NVarChar, Value = lParam.CreatedBy }, //new SqlParameter() {ParameterName = "@CreatedDate", SqlDbType = SqlDbType.NVarChar, Value = lParam.CreatedDate}, new SqlParameter() { ParameterName = "@LastUpdateBy", SqlDbType = SqlDbType.NVarChar, Value = lParam.LastUpdateBy }, //new SqlParameter() {ParameterName = "@LastDate", SqlDbType = SqlDbType.NVarChar, Value = lParam.LastDate} //detail new SqlParameter() { ParameterName = "@Note", SqlDbType = SqlDbType.NVarChar, Value = lParam.Note }, new SqlParameter() { ParameterName = "@Amount", SqlDbType = SqlDbType.Decimal, Value = lParam.Amount }, new SqlParameter() { ParameterName = "@Seq", SqlDbType = SqlDbType.NVarChar, Value = lParam.Seq }, new SqlParameter() { ParameterName = "@DepositTypeID", SqlDbType = SqlDbType.NVarChar, Value = lParam.DepositTypeID } }; String query = @"BEGIN TRAN DELETE FROM [DepositDetail] WHERE DepositID = @DepositID INSERT INTO[DepositDetail] ( DepositID ,Note ,Amount ,Seq ,DepositTypeID ) VALUES( @DepositID ,@Note ,@Amount ,@Seq ,@DepositTypeID ) UPDATE [Deposit] SET VisitID = @VisitID ,EmployeeID = @EmployeeID ,Status = @Status ,ReceivedDate = @ReceivedDate ,Description = @Description ,CustomerID = @CustomerID ,LastUpdateBy = @LastUpdateBy ,LastDate = GETDATE() WHERE DepositID = @DepositID IF @@rowcount = 0 BEGIN INSERT INTO [Deposit] ( DepositID ,VisitID ,EmployeeID ,Status ,ReceivedDate ,Description ,CustomerID ,CreatedBy ,CreatedDate ,IsSMS ) VALUES ( @DepositID ,@VisitID ,@EmployeeID ,@Status ,@ReceivedDate ,@Description ,@CustomerID ,@CreatedBy ,GETDATE() ,0 ) END COMMIT TRAN"; mdlResult.Result = Manager.DataFacade.DTSQLVoidCommand(query, sp); if (mdlResult.Result == "1") { // setelah upload berhasil akan memanggil API sms gateway // sebelumnya cek dahulu apakah status smsnya berhasil atau tidak List <SqlParameter> sp_checkstatus = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@DepositID", SqlDbType = SqlDbType.NVarChar, Value = lParam.DepositID } }; string query_checkstatus = "SELECT IsSMS FROM DEPOSIT WHERE DepositID = @DepositID "; DataTable dtCheck = DataFacade.DTSQLCommand(query_checkstatus, sp_checkstatus); int statusSMS = 0; foreach (DataRow rowCheck in dtCheck.Rows) { statusSMS = Convert.ToInt32(rowCheck["IsSMS"].ToString()); } if (statusSMS == 1) { // do nothing mdlResult.ResultValue = "UPLOAD SUCCESS"; } else { // ambil company name string CompanyName = ""; List <SqlParameter> spCompany = new List <SqlParameter>() { }; string qryCompany = "SELECT * FROM Company WHERE CompanyID = 'BPR'"; DataTable dtCompany = DataFacade.DTSQLCommand(qryCompany, spCompany); foreach (DataRow rowCompany in dtCompany.Rows) { CompanyName = rowCompany["CompanyName"].ToString(); } //default testing string MsisdnSmsGateway = "82298332125"; //string MsisdnSmsGateway = lParam.CustomerMobilephone1; string rekeningNo = lParam.BankAccountNumber; string lTime = DateTime.Now.ToString("yyyyMMddhhmmss"); double value = lParam.Amount; string valueDeli = String.Format(CultureInfo.InvariantCulture, "{0:0,0}", value); string urlAPI = ConfigurationManager.AppSettings["UrlAPISmsGateway"]; string UserSmsGateway = ConfigurationManager.AppSettings["UserSmsGateway"]; string PasswordSmsGateway = ConfigurationManager.AppSettings["PasswordSmsGateway"]; string SenderIDSmsGateway = ConfigurationManager.AppSettings["SenderIDSmsGateway"]; string MessageSmsGateway = CompanyName + "\n" + "SUKSES No=" + lParam.DepositID + "\n" + "Setoran" + "\n" + "Rek=" + rekeningNo + "\n" + "an." + lParam.CustomerName + "\n" + "Tgl=" + lTime + "\n" + "Nom=Rp." + valueDeli; var dict = new Dictionary <string, string>(); dict.Add("user", UserSmsGateway); dict.Add("password", PasswordSmsGateway); dict.Add("senderid", SenderIDSmsGateway); dict.Add("message", MessageSmsGateway); dict.Add("msisdn", MsisdnSmsGateway); string paramSMS = StringFacade.urlencodeString(dict); string resultSMS = StringFacade.PostAPISMSGateway(urlAPI, paramSMS); var DesResult = JsonConvert.DeserializeObject <Model.mdlResultSmsGateway>(resultSMS); if (DesResult.status == "SUCCESS") { mdlResult.ResultValue = "UPLOAD SUCCESS"; //ketika sms berhasil, update status isSMS List <SqlParameter> sp_sms = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@DepositID", SqlDbType = SqlDbType.NVarChar, Value = lParam.DepositID } }; string query_sms = "UPDATE DEPOSIT SET IsSMS = 1 WHERE DepositID = @DepositID"; var mdlResultSms = new Model.mdlResult(); Manager.DataFacade.DTSQLVoidCommand(query_sms, sp_sms); //simpan juga log smsnya LogFacade.InsertLogSMS_Service(urlAPI, paramSMS, resultSMS); } else { mdlResult.ResultValue = "UPLOAD SUCCESS but SMS FAILED"; } } } else { string ResultSubstring; if (mdlResult.Result.Length > 500) { ResultSubstring = mdlResult.Result.Substring(0, 500); mdlResult.Result = ResultSubstring; } } mdlResultList.Add(mdlResult); } var mdlResultListnew = new Model.mdlResultList(); mdlResultListnew.ResultList = mdlResultList; return(mdlResultListnew); }