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