private void BindGrid(string ChapterID, string FiscalYear)
{
DataTable _DT_GrdvwData = new DataTable();
using (SqlService _S = new SqlService())
{
_S.AddParameter(new SqlParameter("@_ChptrIDValue", ChapterID));
_S.AddParameter(new SqlParameter("@_FiscalYrValue", FiscalYear));
_DT_GrdvwData = _S.ExecuteSPDataSet("sp_Insite3_ChapterSelfReportedImpressions_GetData").Tables[0];
grdvwdata.DataSource = _DT_GrdvwData;
grdvwdata.DataBind();
}
if (_DT_GrdvwData.Rows.Count > 0)
{
Lbl_ChptrHdr.Text = _DT_GrdvwData.Rows[0]["ChptrName"].ToString();
Lbl_FiscalHdr.Text = _DT_GrdvwData.Rows[0]["FY"].ToString();
Lbl_ChptrHdr_Print.Text = _DT_GrdvwData.Rows[0]["ChptrName"].ToString();
Lbl_FiscalHdr_Print.Text = _DT_GrdvwData.Rows[0]["FY"].ToString();
Lbl_Region.Text = "Region # " + _DT_GrdvwData.Rows[0]["RegionNum"].ToString();
string RegionAdmin = _DT_GrdvwData.Rows[0]["RegionAdmin"].ToString();
string LockedOut = _DT_GrdvwData.Rows[0]["LockedOut"].ToString();
Accessibility(LockedOut, RegionAdmin);
}
//START - Calculate Sum and display in Footer Row
BindGridFooter(_DT_GrdvwData, grdvwdata);
//END - Calculate Sum and display in Footer Row
}
//End
//keyword
public void InsertT_Keywords(string Keyword, double NewsID, int UserID)
{
string _sql = "CMS_InsertT_Keywords_Dynamic";
try
{
string sql = "Delete T_KeyNews where IDNews = " + NewsID;
HPCDataProvider.Instance().ExecSql(sql);
string[] _Keyword = Keyword.ToString().Trim().Split(',');
for (int i = 0; i < _Keyword.Length; i++)
{
string _key = _Keyword[i].Trim();
if (_key.Length > 1)
{
SqlService _sqlservice = new SqlService();
_sqlservice.AddParameter("@Keyword", SqlDbType.NVarChar, _key, true);
_sqlservice.AddParameter("@NewsID", SqlDbType.Float, NewsID, true);
_sqlservice.AddParameter("@UserID", SqlDbType.Float, UserID, true);
_sqlservice.ExecuteSP(_sql);
_sqlservice.CloseConnect(); _sqlservice.Disconnect();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
} //End Add method
/// <summary>
/// Updates an existing DB record with provided information.
/// </summary>
/// <param name="info"> The SKU record that needs updating. </param>
/// <param name="transaction"> The SQL transaction object. </param>
public void Update(Cont.SKU info, SqlTransaction transaction = null)
{
SqlService sql = null;
try
{
sql = new SqlService(transaction);
sql.AddParameter("@ID", SqlDbType.Int, info.ID, ParameterDirection.Input, true);
sql.AddParameter("@Name", SqlDbType.VarChar, info.Name, 50, ParameterDirection.Input, true);
sql.AddParameter("@Description", SqlDbType.VarChar, info.Description, 50, ParameterDirection.Input, true);
sql.AddParameter("@Commodity", SqlDbType.VarChar, info.Commodity, 50, ParameterDirection.Input, true);
sql.ExecuteSP("UpdateSKU");
// Update cached values
Cont.SKU cacheItemToRemove = CachedValues.Value.FirstOrDefault(x => x.ID == info.ID && !x.Equals(info));
if (cacheItemToRemove != null)
{
CachedValues.Value.Remove(cacheItemToRemove); //Remove item
CachedValues.Value.Add(info); //Add item
}//end if
}//end try
catch (Exception exc)
{
throw exc;
}//end catch
finally
{
if (sql != null)
{
sql.Disconnect();
}
} //end finally
} //End update method
/// <summary>
/// Adds a SKU record to the database.
/// </summary>
/// <param name="info"> The SKU record that needs adding. </param>
/// <param name="transaction"> The SQL transaction object. </param>
/// <returns> The DB record ID. </returns>
public int Add(Cont.SKU info, SqlTransaction transaction = null)
{
SqlService sql = null;
try
{
sql = new SqlService(transaction);
sql.AddParameter("@ID", SqlDbType.Int, info.ID, ParameterDirection.InputOutput, true);
sql.AddParameter("@Name", SqlDbType.VarChar, info.Name, 50, ParameterDirection.Input, true);
sql.AddParameter("@Description", SqlDbType.VarChar, info.Description, 50, ParameterDirection.Input, true);
sql.AddParameter("@Commodity", SqlDbType.VarChar, info.Commodity, 50, ParameterDirection.Input, true);
sql.ExecuteSP("AddSKU");
SqlParameter param = sql.ResultParameters["@ID"];
info.ID = Convert.ToInt32(param.Value);
// Update cached values
CachedValues.Value.Add(info);
}//end try
catch (Exception exc)
{
throw exc;
}//end catch
finally
{
if (sql != null)
{
sql.Disconnect();
}
}//end finally
return(info.ID);
} //End Add method
public UserUpdateResponse UpdateUser(UsersUpdateRequest request, int id)
{
var sqlProvider = new SqlService();
var s3Ser = new S3Service();
var sliceImgUrl = "";
if (request.ImgFileType != null)
{
sliceImgUrl = s3Ser.SignedUrlWithNoExpire(null);
}
sqlProvider.AddParameter("@UserId", id);
sqlProvider.AddParameter("@Name", request.Name);
sqlProvider.AddParameter("@Email", request.Email);
sqlProvider.AddParameter("@ImageUrl", sliceImgUrl);
sqlProvider.ExecuteNonQuery("Users_Update");
var user = new UserUpdateResponse
{
Id = id,
ImageSignedUrl = s3Ser.GeneratePreSignedURL("UI" + id.ToString(), request.ImgFileType)
};
return(user);
}
//ICMS_spUpdateReportRecipients
//@pId as uniqueidentifier = null,
//@pFirstName as varchar(50) = null,
//@pLastName as varchar(50) = null,
//@pRole as varchar(50) = null,
//@pEmail as varchar(50) = null
/// <summary>
/// Add a new report recipient
/// </summary>
/// <param name="recipient"></param>
public void AddRecipient(Recipient recipient)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pName", System.Data.SqlDbType.VarChar, recipient.Name);
sql.AddParameter("@pRole", System.Data.SqlDbType.VarChar, recipient.Role);
sql.AddParameter("@pEmail", System.Data.SqlDbType.VarChar, recipient.Email);
sql.ExecuteSP("ICMS_spUpdateReportRecipients");
}
public bool UpdatePassword(UsersUpdateRequest request)
{
var sqlProvider = new SqlService();
sqlProvider.AddParameter("@UserId", request.Id);
sqlProvider.AddParameter("@Password", request.Password);
sqlProvider.AddParameter("@NewPassword", request.NewPassword);
return((bool)sqlProvider.ExecuteScalar("User_Update_Password"));
}
public int DeleteVote(VoteRequest vote)
{
var sqlService = new SqlService();
sqlService.AddParameter("@LyricsId", vote.LyricsId);
sqlService.AddParameter("@UserId", vote.VoterId);
int retId = (int)sqlService.ExecuteScalar("Votes_Delete_ByVoterId");
return(retId);
}
public bool PasswordReset(UsersUpdateRequest request)
{
var sql = new SqlService();
sql.AddParameter("@UserId", request.Id);
sql.AddParameter("@Password", request.Password);
bool ret = sql.ExecuteScalar("Users_ResetPassword") == DBNull.Value ? false : true;
return(ret);
}
public int VoteUp(VoteRequest vote)
{
var sqlService = new SqlService();
sqlService.AddParameter("@LyricsId", vote.LyricsId);
sqlService.AddParameter("@UserId", vote.VoterId);
var retId = (int)sqlService.ExecuteScalar("Votes_Insert");
return(retId);
}
public bool ValidateAccount(string key)
{
var sql = new SqlService();
sql.AddParameter("@EmailVerified", true);
sql.AddParameter("@VerificationKey", key);
bool ret = sql.ExecuteScalar("User_Update_EmailVerified") == DBNull.Value ? false : true;
return(ret);
}
public DataSet GetCategories()
{
SqlService sql = new SqlService();
sql.AddParameter("@Lang", SqlDbType.NVarChar, Lang);
sql.AddParameter("Cat_ID", SqlDbType.NVarChar, Cat_ID);
return(sql.ExecuteSqlDataSet(@"SELECT * FROM Category_Table C
INNER JOIN Language_Table LT
ON LT.Lang_ID = C.Cat_Lang
AND LT.Lang_Code = @Lang
WHERE C.Cat_ID IN(SELECT Element FROM func_Split(@Cat_ID, ','))"));
}
public List <SMS_Model> Get_SMS_Report(string Sent_Date, string SMS_Category)
{
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Sent_Date", SqlDbType.VarChar, Sent_Date);
sqlService.AddParameter("@SMS_Category", SqlDbType.VarChar, SMS_Category);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_SMS_Report"))
{
return(sqlDataReader.MapToList <SMS_Model>());
}
}
}
public short SaveClassFeeSetting(Class_Fee_Setting_Model classFeeSettingModels)
{
short result;
try
{
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassFeeID", classFeeSettingModels.ClassFeeID);
sqlService.AddParameter("@ClassID", classFeeSettingModels.ClassID);
sqlService.AddParameter("@FeeTypeID", classFeeSettingModels.FeeTypeID);
sqlService.AddParameter("@FeeAmount", classFeeSettingModels.FeeAmount);
sqlService.AddParameter("@Active", classFeeSettingModels.Active);
sqlService.AddParameter("@IsApplicableOnStaffChild", classFeeSettingModels.IsApplicableOnStaffChild);
sqlService.AddParameter("@AmountForStaffChild", classFeeSettingModels.AmountForStaffChild);
sqlService.AddParameter("@Academic_Year", classFeeSettingModels.AcademicYear);
sqlService.AddOutputParameter("@Result", SqlDbType.SmallInt);
sqlService.ExecuteSPNonQuery("dbo.USP_SaveClassFeeSetting");
result = (short)sqlService.Parameters["@Result"].Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public int SaveExpenses(ExpensesModel model)
{
int result;
try
{
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@AutoExpensesID", model.AutoExpensesID);
sqlService.AddParameter("@Particulars", model.Particulars);
sqlService.AddParameter("@ExpensesBillNo", model.ExpensesBillNo);
sqlService.AddParameter("@ShopName", model.ShopName);
sqlService.AddParameter("@Quantity", model.Quantity);
sqlService.AddParameter("@Amount", model.Amount);
sqlService.AddParameter("@PaymentMode", model.PaymentMode);
sqlService.AddParameter("@PurchasesDate", model.PurchasesDate);
sqlService.AddOutputParameter("@Result", SqlDbType.SmallInt);
sqlService.ExecuteSPNonQuery("dbo.USP_SaveExpenses");
result = (int)((short)sqlService.Parameters["@Result"].Value);
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
private void BindHistoryGrid(string ChapterID, string FiscalYear)
{
using (SqlService _S = new SqlService())
{
//START - Bind History Grid
_S.AddParameter(new SqlParameter("@_ChptrIDValue_H", ChapterID));
_S.AddParameter(new SqlParameter("@_FiscalYrValue_H", FiscalYear));
DataTable _DT_GridHistory = _S.ExecuteSPDataSet("sp_Insite3_ChapterSelfReportedImpressions_GetHistory").Tables[0];
grdvwdata_history.DataSource = _DT_GridHistory;
grdvwdata_history.DataBind();
//END - Bind History Grid
}
}
public DataSet GetPurchasesFeeReport(long ReceiptNo, long RegistrationNo)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ReceiptNo", ReceiptNo);
sqlService.AddParameter("@RegistrationNo", RegistrationNo);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_ReportPurchasesFeeReceipt"))
{
result = dataSet;
}
}
return(result);
}
public List <ClassFeeSettingViewModel> GetPurchasableFeeTypeAmount(short?ClassID, short FeeTypeID)
{
List <ClassFeeSettingViewModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassID", SqlDbType.TinyInt, ClassID);
sqlService.AddParameter("@FeeTypeID", SqlDbType.TinyInt, FeeTypeID);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetPurchasableFeeTypeAmount"))
{
result = sqlDataReader.MapToList <ClassFeeSettingViewModel>();
}
}
return(result);
}
public DataSet Get_Student_Fee_Info_Report(int Academic_Year, string Fee_Info_Type)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Academic_Year", SqlDbType.Int, Academic_Year);
sqlService.AddParameter("@Fee_Info_Type", SqlDbType.VarChar, Fee_Info_Type);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_Student_Fee_Info_Report"))
{
result = dataSet;
}
}
return(result);
}
public List <Student_Fee_Info_Months_Model> Get_Student_Fee_Setting_Months(long StudentID, int Academic_Year)
{
List <Student_Fee_Info_Months_Model> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Student_ID", SqlDbType.BigInt, StudentID);
sqlService.AddParameter("@Academic_Year", SqlDbType.Int, Academic_Year);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Fee_Setting_Months"))
{
result = sqlDataReader.MapToList <Student_Fee_Info_Months_Model>();
}
}
return(result);
}
public DataSet GetStudentDetailsByClass(short?ClassID, short?SectionID)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassID", ClassID);
sqlService.AddParameter("@SectionID", SectionID);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_GetStudentDetailsByClass"))
{
result = dataSet;
}
}
return(result);
}
/// <summary>
/// MMD_spLogPlanningOfficerMessage
/// </summary>
public void LogMessage()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pMessage", System.Data.SqlDbType.VarChar, Message);
sql.AddParameter("@pCustomerMobile", System.Data.SqlDbType.BigInt, CustomerMobile);
sql.AddParameter("@pDateSent", System.Data.SqlDbType.DateTime, DateSent);
try
{
sql.ExecuteSP("MMD_spLogPlanningOfficerMessage");
}
catch (Exception ex)
{
throw new Exception("PlanningOfficerMessage.LogMessage: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public bool CheckDuplicatePurchasesFee(long?StudentID, short FeeTypeID, DateTime PurchasesDate)
{
this._feeDepositViewModel = new FeeDepositViewModel();
bool result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@StudentID", SqlDbType.BigInt, StudentID);
sqlService.AddParameter("@FeeTypeID", SqlDbType.SmallInt, FeeTypeID);
sqlService.AddParameter("@PurchasesDate", SqlDbType.Date, PurchasesDate);
string value = sqlService.ExecuteSPScalar("dbo.USP_CheckDuplicatePurchasesFee");
result = (Convert.ToInt16(value) == 0);
}
return(result);
}
public DataSet Get_Advance_Fee_Pay_Report(long Student_ID, long Receipt_No)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Student_Id", SqlDbType.BigInt, Student_ID);
sqlService.AddParameter("@Receipt_No", SqlDbType.BigInt, Receipt_No);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_Get_Advance_Pay_Report"))
{
result = dataSet;
}
}
return(result);
}
public User_Model Get_User_Login_Details(string User_ID, string Password)
{
User_Model result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@User_ID", User_ID);
sqlService.AddParameter("@Password", Password);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_User_Login"))
{
result = sqlDataReader.MapToSingle <User_Model>();
}
}
return(result);
}
private void GetTodaysOrders()
{
_todaysOrders = new SortedList<long, Order>();
Order order;
SqlService sql = new SqlService(_sqlConnection);
if (_customerKey != null)
{
sql.AddParameter("@pCustKey", System.Data.SqlDbType.UniqueIdentifier, _customerKey);
}
//TODO fix stored procedure to get todays orders
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetTodaysOrders"))
{
if (reader.HasRows)
{
while (reader.Read())
{
order = new Order();
order.IndentNo = reader.SafeGetInt64(reader.GetOrdinal("indentNo")).Value;
order.InvoiceNo = reader.SafeGetString(reader.GetOrdinal("invoiceNo"));
//order.OrderKey = reader.SafeGetGuid(reader.GetOrdinal("orderKey")).Value;
order.DeliveryDate = reader.SafeGetString(reader.GetOrdinal("deliveryDate"));
order.OrderNo = reader.SafeGetString(reader.GetOrdinal("orderNo"));
order.Status = reader.SafeGetString(reader.GetOrdinal("orderStatus"));
order.TTNo = reader.SafeGetString(reader.GetOrdinal("orderTTNo"));
order.Customer = reader.SafeGetString(reader.GetOrdinal("customer"));
order.SalesArea = reader.SafeGetString(reader.GetOrdinal("ordAreaCode"));
order.TTOutTime = reader.SafeGetString(reader.GetOrdinal("orderTTOut"));
_todaysOrders.Add(order.IndentNo, order);
}
}
}
}
public DataSet GetFeeReportByMonth(FeeMonthlyReportModel feeMonthlyReportModel)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassID", feeMonthlyReportModel.ClassID);
sqlService.AddParameter("@Year", feeMonthlyReportModel.Year);
sqlService.AddParameter("@Month", feeMonthlyReportModel.Month);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_Report_Monthly_Fee_By_Class_Multiple_Months"))
{
result = dataSet;
}
}
return(result);
}
public List <Student_Fee_Info_Months_Model> Get_Student_Fee_Setting_Details_By_Receipt_No(long?Receipt_No, string Fee_Info_Code, int Academic_Year)
{
List <Student_Fee_Info_Months_Model> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Receipt_No", SqlDbType.BigInt, Receipt_No);
sqlService.AddParameter("@Fee_Info_Code", SqlDbType.VarChar, Fee_Info_Code);
sqlService.AddParameter("@Academic_Year", SqlDbType.Int, Academic_Year);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Fee_Info_By_Receipt_No"))
{
result = sqlDataReader.MapToList <Student_Fee_Info_Months_Model>();
}
}
return(result);
}
private void GetOrderCountSummary(Guid? customerKey)
{
//TODO get the order count summary from the database
//_orderCountSummary = new Dictionary<string, int>();
//_orderCountSummary["All"] = 0;
//_orderCountSummary["Closed"] = 0;
//_orderCountSummary["Allocated"] = 0;
//_orderCountSummary["Not Allocated"] = 0;
_orderCountSummary = new Dictionary<string, int>();
SqlService sql = new SqlService(_sqlConnection);
//TODO Get from logged in user
if (customerKey != null)
{
sql.AddParameter("@pCustKey", System.Data.SqlDbType.UniqueIdentifier, customerKey);
}
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetOrderStatusReview"))
{
if (reader.HasRows)
{
while (reader.Read())
{
_orderCountSummary[reader.SafeGetString(reader.GetOrdinal("orderStatus"))] = reader.SafeGetInt(reader.GetOrdinal("ordCount")).Value;
}
}
}
}
public List <ClassFeeSettingViewModel> GetClassFeeSetting(short?ClassID, short?FeeTypeID, int AcademicYear)
{
List <ClassFeeSettingViewModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassID", SqlDbType.TinyInt, ClassID);
sqlService.AddParameter("@FeeTypeID", SqlDbType.TinyInt, FeeTypeID);
sqlService.AddParameter("@AcademicYear", SqlDbType.Int, AcademicYear);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetClassFeeSetting"))
{
result = sqlDataReader.MapToList <ClassFeeSettingViewModel>();
}
}
return(result);
}
public User Create(UsersCreateRequest request)
{
var retUser = new User();
var sql = new SqlService();
sql.AddParameter("@Name", request.Name);
sql.AddParameter("@Email", request.Email);
sql.AddParameter("@Password", request.Password);
retUser.Id = (int)sql.ExecuteScalar("Users_Insert");
retUser.Name = request.Name;
retUser.Email = request.Email;
return(retUser);
}
public FeeDepositViewModel GetFeeDetailsByReceiptNo(long?ReceiptNo)
{
this._feeDepositViewModel = new FeeDepositViewModel();
try
{
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ReceiptNo", SqlDbType.BigInt, ReceiptNo);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetFeeDetailsByReceiptNo"))
{
this._feeDepositViewModel.FeeDepositModel = sqlDataReader.MapToSingle <FeeDepositModel>();
sqlDataReader.NextResult();
this._feeDepositViewModel.ListFeeApplicable = sqlDataReader.MapToList <Class_Fee_Setting_Model>();
sqlDataReader.NextResult();
this._feeDepositViewModel.ListFeeDues = sqlDataReader.MapToList <MonthlyFeeGenerateModel>();
sqlDataReader.NextResult();
this._feeDepositViewModel.ListMonthlyTransaction = sqlDataReader.MapToList <MonthlyFeeDepositModel>();
sqlDataReader.NextResult();
this._feeDepositViewModel.ListStudentFine = sqlDataReader.MapToList <StudentFineModel>();
sqlDataReader.NextResult();
this._feeDepositViewModel.ListStudentFinePaid = sqlDataReader.MapToList <StudentFineModel>();
sqlDataReader.NextResult();
this._feeDepositViewModel.List_Fee_Deposited = sqlDataReader.MapToList <MonthlyFeeDepositModel>();
sqlDataReader.NextResult();
}
}
}
catch (Exception var_2_107)
{
}
return(this._feeDepositViewModel);
}
public DataSet GetStudentPromotionReport(short ClassID, short SectionID, int From_Academic_Year)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@ClassID", ClassID);
sqlService.AddParameter("@SectionID", SectionID);
sqlService.AddParameter("@From_Academic_Year", From_Academic_Year);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_GetStudentPromotionReport"))
{
result = dataSet;
}
}
return(result);
}
public Student_Fee_Info_Receipt_Model Get_Student_Fee_Info_Receipt_No(long StudentID, int Academic_Year, string Fee_Info_Code)
{
Student_Fee_Info_Receipt_Model result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Student_ID", SqlDbType.BigInt, StudentID);
sqlService.AddParameter("@Academic_Year", SqlDbType.Int, Academic_Year);
sqlService.AddParameter("@Fee_Info_Code", SqlDbType.VarChar, Fee_Info_Code);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Fee_Info_Receipt_No"))
{
result = sqlDataReader.MapToSingle <Student_Fee_Info_Receipt_Model>();
}
}
return(result);
}
public SalesArea GetSalesArea(Guid id)
{
SalesArea s = new SalesArea();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
using (SqlDataReader r = sql.ExecuteSPReader("ICMS_spGetSalesAreas"))
{
if (r.HasRows)
{
while (r.Read())
{
SetupSalesArea(s, r);
}
}
}
return s;
}
public PaymentTerm GetPaymentTerm(Guid id)
{
PaymentTerm p = new PaymentTerm();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetPaymentTerms"))
{
if (reader.HasRows)
{
while (reader.Read())
{
SetupPaymentTerm(p, reader);
}
}
}
return p;
}
public Contractor GetContractor(Guid id)
{
Contractor c = new Contractor();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetContractors"))
{
if (reader.HasRows)
{
while (reader.Read())
{
SetupContractor(c, reader);
}
}
}
return c;
}
public Driver GetDriver(Guid id)
{
Driver driver = new Driver();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pDriverKey", System.Data.SqlDbType.UniqueIdentifier, id);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetDriver"))
{
if (reader.HasRows)
{
while (reader.Read())
{
driver.Id = id; // reader.SafeGetGuid(reader.GetOrdinal("DriverKey")).Value;
driver.DriverName = reader.SafeGetString(reader.GetOrdinal("DriverName"));
driver.DriverMobileNo = reader.SafeGetString(reader.GetOrdinal("DriverMobile"));
driver.AssignedTT = reader.SafeGetString(reader.GetOrdinal("AssignedTT"));
}
}
}
return driver;
}
/// <summary>
/// MMD_spCheckMessageIdLog
/// @pMessageId
/// </summary>
public bool CheckMessageId()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pMessageId", System.Data.SqlDbType.VarChar, _messageId);
bool isFound;
try
{
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spCheckMessageIdLog"))
{
reader.Read();
if (Convert.ToBoolean(reader.GetValue(0)))
isFound = true;
else
isFound = false;
}
}
catch (Exception ex)
{
throw new Exception("CheckMessageId: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
return isFound;
}
public User GetUser(string id)
{
User user = new User();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.NVarChar, id);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetUser"))
{
if (reader.HasRows)
{
while (reader.Read())
{
user.Id = id;
user.UserName = reader.SafeGetString(reader.GetOrdinal("UserName"));
user.FirstName = reader.SafeGetString(reader.GetOrdinal("FirstName"));
user.LastName = reader.SafeGetString(reader.GetOrdinal("LastName"));
user.Email = reader.SafeGetString(reader.GetOrdinal("Email"));
user.MobileNo = reader.SafeGetInt64(reader.GetOrdinal("MobileNo"));
user.CompanyName = reader.SafeGetString(reader.GetOrdinal("CompanyName"));
user.Role = reader.SafeGetString(reader.GetOrdinal("Role"));
}
}
}
return user;
}
public IList<RTKM> GetRTKMReport(OwnerType ownerType)
{
IList<RTKM> list = new List<RTKM>();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pOwnerType", System.Data.SqlDbType.TinyInt, (int)ownerType);
using (SqlDataReader r = sql.ExecuteSPReader("ICMS_spReport_RTKM"))
{
if (r.HasRows)
{
while (r.Read())
{
RTKM rtkm = new RTKM();
rtkm.TTRef = r.SafeGetString(r.GetOrdinal("ref"));
rtkm.TTID = r.SafeGetString(r.GetOrdinal("id"));
rtkm.TTNo = r.SafeGetString(r.GetOrdinal("no"));
rtkm.Distance = r.SafeGetInt(r.GetOrdinal("km")).Value;
rtkm.Trips = r.SafeGetInt(r.GetOrdinal("trips")).Value;
rtkm.Quantity = r.SafeGetInt(r.GetOrdinal("qty")).Value;
list.Add(rtkm);
}
}
}
return list;
}
private void CreateUpdateUser(User user)
{
//ICMS_spCreateUpdateUser
SqlService sql = new SqlService(_sqlConnection);
if (!string.IsNullOrEmpty(user.Id))
{
sql.AddParameter("@pId", System.Data.SqlDbType.NVarChar, user.Id);
}
sql.AddParameter("@pUserName", System.Data.SqlDbType.VarChar , user.UserName);
sql.AddParameter("@pFirstName", System.Data.SqlDbType.VarChar , user.FirstName);
sql.AddParameter("@pLastName", System.Data.SqlDbType.VarChar , user.LastName);
sql.AddParameter("@pEmail", System.Data.SqlDbType.VarChar , user.Email);
sql.AddParameter("@pMobileNo", System.Data.SqlDbType.BigInt , user.MobileNo);
sql.AddParameter("@pCompanyName", System.Data.SqlDbType.VarChar , user.CompanyName);
//sql.AddParameter("@pCustomerKey", System.Data.SqlDbType.UniqueIdentifier , user.CustomerKey);
sql.AddParameter("@pRole", System.Data.SqlDbType.VarChar, user.Role);
sql.ExecuteSP("ICMS_spCreateUpdateUser");
}
private void CreateUpdateDriver(Driver driver)
{
// MMD_spUpdateDriver]
//@pDriverKey as uniqueidentifier = NULL,
//@pTTKey as uniqueidentifier = NULL,
//@pTTNo as varchar(20),
//@pDriverName as varchar(250),
//@pDriverMobile as bigint
SqlService sql = new SqlService(_sqlConnection);
if (driver.Id != null)
sql.AddParameter("@pDriverKey", System.Data.SqlDbType.UniqueIdentifier, driver.Id);
sql.AddParameter("@pTTNo", System.Data.SqlDbType.VarChar, driver.AssignedTT);
sql.AddParameter("@pDriverName", System.Data.SqlDbType.VarChar, driver.DriverName);
sql.AddParameter("@pDriverMobile", System.Data.SqlDbType.VarChar, driver.DriverMobileNo);
sql.ExecuteSP("MMD_spUpdateDriver");
}
private Guid MergeAccessIdentity(Enrollment model)
{
// var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlService sql = new SqlService(sqlConnectionString);
//TODO add other fields
//TODO registration ID
sql.AddParameter("@pIdentityType", System.Data.SqlDbType.VarChar, model.IdentityType);
sql.AddParameter("@pShiftOperation", System.Data.SqlDbType.VarChar, model.Shift);
sql.AddParameter("@pBloodGroup", System.Data.SqlDbType.VarChar, model.BloodGroup);
sql.AddParameter("@pName", System.Data.SqlDbType.VarChar, model.Name);
sql.AddParameter("@pEmplNo", System.Data.SqlDbType.VarChar, model.EmployeeNo);
sql.AddParameter("@pMobileNo", System.Data.SqlDbType.VarChar, model.MobileNo);
sql.AddParameter("@pCompanyRef", System.Data.SqlDbType.VarChar, model.CompanyRefNo);
sql.AddParameter("@pCompanyName", System.Data.SqlDbType.VarChar, model.CompanyName);
sql.AddParameter("@pTransporterName", System.Data.SqlDbType.VarChar, model.TransporterName);
sql.AddParameter("@pTransporterMobileNo", System.Data.SqlDbType.VarChar, model.TransportMobileNo);
sql.AddParameter("@pContractorName", System.Data.SqlDbType.VarChar, model.ContractorName);
sql.AddParameter("@pContractorMobileNo", System.Data.SqlDbType.VarChar, model.ContractorMobileNo);
sql.AddParameter("@pResidentialPhone", System.Data.SqlDbType.VarChar, model.ResidentialNo);
sql.AddParameter("@pPermanentAddress", System.Data.SqlDbType.VarChar, model.PermanentAddress);
sql.AddParameter("@pResidentialAddress", System.Data.SqlDbType.VarChar, model.ResidentialAddress);
sql.AddParameter("@pLicenseExpiryDate", System.Data.SqlDbType.DateTime2, model.LicenseExpiryDate);
sql.AddParameter("@pTTName", System.Data.SqlDbType.VarChar, model.TTName);
sql.AddParameter("@pOfficerNumber", System.Data.SqlDbType.VarChar, model.OfficerStaffMobileNumber);
sql.AddParameter("@pPoliceVerified", System.Data.SqlDbType.Bit, model.PoliceVerification);
sql.AddParameter("@pPoliceVerDate", System.Data.SqlDbType.Bit, model.PoliceVerificationDate);
sql.AddParameter("@pESICNo", System.Data.SqlDbType.Bit, model.ESICNo);
sql.AddParameter("@pPONo", System.Data.SqlDbType.Bit, model.PurchaseOrderNo);
sql.AddParameter("@pPOStartDate", System.Data.SqlDbType.Bit, model.PODateStart);
sql.AddParameter("@pPOEndDate", System.Data.SqlDbType.Bit, model.PODateEnd);
sql.AddParameter("@pJobSkill", System.Data.SqlDbType.Bit, model.JobSkill);
sql.AddParameter("@pPFNo", System.Data.SqlDbType.Bit, model.PFNo);
sql.AddParameter("@pAadhaar", System.Data.SqlDbType.Bit, model.AadhaarNo);
sql.AddParameter("@pBankName", System.Data.SqlDbType.Bit, model.BankName);
sql.AddParameter("@pACNo", System.Data.SqlDbType.Bit, model.ACNo);
sql.AddParameter("@pIFSCCode", System.Data.SqlDbType.Bit, model.IFSCCode);
if (model.RegistrationId != null)
{
sql.AddParameter("@pRegistrationId", System.Data.SqlDbType.UniqueIdentifier, model.RegistrationId);
}
//sql.AddParameter("@pPhoto", System.Data.SqlDbType.VarChar, model.Photo);
//sql.AddParameter("@pAddressProof", System.Data.SqlDbType.VarChar, model.Proof);
using (SqlDataReader reader = sql.ExecuteSPReader("MMD_spCreateAccessIdentity"))
{
if (reader.HasRows)
{
reader.Read();
return reader.GetGuid(0);
}
else
return Guid.Empty;
}
}
private string LogAccessIdentity(string result, string gateId)
{
Guid accessId;
if (Guid.TryParse(result, out accessId))
{
//Log access
//ICMS_spLogTerminalAccess
//@pAccessId as uniqueidentifier,
//@pGateId as uniqueidentifier)
string accessIdentityName = string.Empty;
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlService sql = new SqlService(sqlConnectionString);
sql.AddParameter("@pAccessId", System.Data.SqlDbType.UniqueIdentifier, accessId);
sql.AddParameter("@pGateId", System.Data.SqlDbType.VarChar, gateId);
using (SqlDataReader reader = sql.ExecuteSPReader("TEMS_spLogTerminalAccess"))
{
if (reader.HasRows)
{
reader.Read();
accessIdentityName = reader.GetString(0);
return string.Format("Thank you {0}. Your access has been logged.", accessIdentityName);
}
return "Your identity is not recognized. Please see an administrator for enrollment in the identification system.";
}
}
else
{
return "Your identity is not recognized. Please see an administrator for enrollment in the identification system.";
}
}
private void CreateUpdatePaymentTerm(PaymentTerm p)
{
SqlService sql = new SqlService(_sqlConnection);
if (p.Id != null)
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, p.Id);
sql.AddParameter("@pCode", System.Data.SqlDbType.VarChar, p.Code);
sql.AddParameter("@pDescription", System.Data.SqlDbType.VarChar, p.Description);
sql.ExecuteSP("ICMS_spCreateUpdatePaymentTerm");
}
private void GetBolDetails()
{
int indentOut = 0;
string product = string.Empty;
int amount = 0;
if (int.TryParse(IndentNo, out indentOut))
{
SqlService sql = new SqlService(_sqlConnection);
//TODO Get from logged in user
sql.AddParameter("@pIndentNo", System.Data.SqlDbType.Int, indentOut);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_BolDetails"))
{
if (reader.HasRows)
{
while (reader.Read())
{
//TODO get order details
OrderStatus = reader.SafeGetString(reader.GetOrdinal("status_desc"));
OrderNo = reader.SafeGetString(reader.GetOrdinal("order_no"));
DeliveryDate = reader.SafeGetString(reader.GetOrdinal("delivery_date"));
SalesArea = reader.SafeGetString(reader.GetOrdinal("sales_area"));
Customer = reader.SafeGetString(reader.GetOrdinal("customer"));
PaymentTerms = reader.SafeGetString(reader.GetOrdinal("payment_terms"));
TankTruck = reader.SafeGetString(reader.GetOrdinal("truck_no"));
}
if (reader.NextResult())
{
OrderedProducts = new Dictionary<string, int>();
while (reader.Read())
{
product = reader.SafeGetString(reader.GetOrdinal("product"));
amount = reader.SafeGetInt(reader.GetOrdinal("amount")).Value;
OrderedProducts[product] = amount;
}
}
}
}
}
else
throw new NotFiniteNumberException("GetBolDetails: Indent not a number");
}
public Recipient GetRecipient(Guid id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
Recipient r = new Recipient();
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetSMSRecipient"))
{
if (reader.HasRows)
{
while (reader.Read())
{
Int64 mobileOut = 0;
if (Int64.TryParse(reader.SafeGetString(reader.GetOrdinal("MobileNo")), out mobileOut))
{
r.Id = id;
r.FirstName = reader.SafeGetString(reader.GetOrdinal("FirstName"));
r.LastName = reader.SafeGetString(reader.GetOrdinal("LastName"));
r.NickName = reader.SafeGetString(reader.GetOrdinal("NickName"));
r.MobileNo = mobileOut;
r.GroupName = reader.SafeGetString(reader.GetOrdinal("GroupName"));
r.GroupId = reader.SafeGetGuid(reader.GetOrdinal("GroupId")).Value;
}
}
}
}
return r;
}
private Enrollment GetIdentity(Guid registrationId)
{
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
Enrollment id = new Enrollment();
SqlService sql = new SqlService(sqlConnectionString);
sql.AddParameter("pRegistrationId", System.Data.SqlDbType.UniqueIdentifier, registrationId);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetBioIdentity"))
{
if (reader.HasRows)
{
reader.Read();
id.RegistrationId = registrationId;
id.BloodGroup = reader.GetString(reader.GetOrdinal("BloodGroup"));
id.Name = reader.GetString(reader.GetOrdinal("Name")); //reader.GetString(1);
id.MobileNo = reader.GetString(reader.GetOrdinal("MobileNo"));
id.IdentityType = reader.GetString(reader.GetOrdinal("IdentityType"));
id.Shift = reader.GetString(reader.GetOrdinal("ShiftOperation"));
id.CompanyName = reader.GetString(reader.GetOrdinal("CompanyName"));
id.CompanyRefNo = reader.GetString(reader.GetOrdinal("CompanyRefNo"));
id.TransporterName = reader.GetString(reader.GetOrdinal("TransporterName"));
id.TransportMobileNo = reader.GetString(reader.GetOrdinal("TransporterMobileNo"));
id.ContractorName = reader.GetString(reader.GetOrdinal("ContractorName"));
id.ContractorMobileNo = reader.GetString(reader.GetOrdinal("ContractorMobileNo"));
id.ResidentialNo = reader.GetString(reader.GetOrdinal("ResidentialNo"));
id.PermanentAddress = reader.GetString(reader.GetOrdinal("PermanentAddress"));
id.ResidentialAddress = reader.GetString(reader.GetOrdinal("ResidentialAddress"));
id.LicenseExpiryDate = reader.GetDateTime(reader.GetOrdinal("LicenseExpiryDate"));
id.TTName = reader.GetString(reader.GetOrdinal("TTName"));
id.OfficerStaffMobileNumber = reader.GetString(reader.GetOrdinal("OfficerStaffMobileNo"));
//TODO photo and addreess proff
id.Photo_File = reader.GetString(reader.GetOrdinal("Photo"));
id.Proof_File = reader.GetString(reader.GetOrdinal("AddressProof"));
id.PoliceVerification = reader.GetBoolean(reader.GetOrdinal("PoliceVerified"));
id.PoliceVerificationDate = reader.GetDateTime(reader.GetOrdinal("PoliceVerificationDate"));
id.ESICNo = reader.GetString(reader.GetOrdinal("ESICNo"));
id.PurchaseOrderNo = reader.GetString(reader.GetOrdinal("PONo"));
DateTime tempDate;
if (DateTime.TryParse(reader.GetString(reader.GetOrdinal("POStartDate")), out tempDate))
{
id.PODateStart = tempDate;
}
if (DateTime.TryParse(reader.GetString(reader.GetOrdinal("POStartDate")), out tempDate))
{
id.PODateEnd = tempDate;
}
id.JobSkill = reader.GetString(reader.GetOrdinal("JobSkill"));
id.PFNo = reader.GetString(reader.GetOrdinal("PFNo"));
id.AadhaarNo = reader.GetString(reader.GetOrdinal("AadhaarNo"));
id.BankName = reader.GetString(reader.GetOrdinal("BankName"));
id.ACNo = reader.GetString(reader.GetOrdinal("ACNo"));
id.IFSCCode = reader.GetString(reader.GetOrdinal("IFSCCode"));
//id.LastInTime = reader.GetDateTime(14);
//id.LastOutTime = reader.GetDateTime(15);
}
}
return id;
}
public void DeleteRecipient(Guid id)
{
//ICMS_spDeleteSMSRecipient
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
sql.ExecuteSP("ICMS_spDeleteSMSRecipient");
}
private void CreateUpdateRecipient(Recipient recipient)
{
// @pId as uniqueidentifier = null,
//@pFirstName as nvarchar(50) = null,
//@pLastName as nvarchar(50) = null,
//@pNickName as nvarchar(50),
//@pMobileNo as nvarchar(50),
//@pGroupName as varchar(50)
SqlService sql = new SqlService(_sqlConnection);
if (recipient.Id != null)
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, recipient.Id);
sql.AddParameter("@pGroupId", System.Data.SqlDbType.UniqueIdentifier, recipient.GroupId);
sql.AddParameter("@pFirstName", System.Data.SqlDbType.VarChar, recipient.FirstName);
sql.AddParameter("@pLastName", System.Data.SqlDbType.VarChar, recipient.LastName);
sql.AddParameter("@pNickName", System.Data.SqlDbType.VarChar, recipient.NickName);
sql.AddParameter("@pMobileNo", System.Data.SqlDbType.VarChar, recipient.MobileNo);
sql.ExecuteSP("ICMS_spCreateUpdateSMSRecipient");
}
private Guid CreateVisitorPass(Visitor model, Guid registrationId)
{
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlService sql = new SqlService(sqlConnectionString);
sql.AddParameter("pRegistrationId", System.Data.SqlDbType.UniqueIdentifier, registrationId);
sql.AddParameter("pToSee", System.Data.SqlDbType.VarChar, model.ToSee);
sql.AddParameter("pDepartment", System.Data.SqlDbType.VarChar, model.Department);
sql.AddParameter("pPurpose", System.Data.SqlDbType.VarChar, model.Purpose);
sql.AddParameter("pIssuedBy", System.Data.SqlDbType.VarChar, User.Identity.Name);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spCreateVisitorPass"))
{
if (reader.HasRows)
{
reader.Read();
return reader.GetGuid(0);
}
}
return Guid.Empty;
}
private void UpdatePhotoProof(Guid registrationId, string photo_file = "", string proof_file = "")
{
// TEMS_spUpdatePhotoProof
// @pRegistrationId as uniqueidentifier,
//@pPhotoFile as varchar(max) = null,
//@pProofFile as varchar(max) = null
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlService sql = new SqlService(sqlConnectionString);
sql.AddParameter("@pRegistrationId", System.Data.SqlDbType.UniqueIdentifier, registrationId);
sql.AddParameter("@pPhotoFile", System.Data.SqlDbType.VarChar, photo_file);
sql.AddParameter("@pProofFile", System.Data.SqlDbType.VarChar, proof_file);
sql.ExecuteSP("TEMS_spUpdatePhotoProof");
}
/// <summary>
/// ICMS_spGetCustHistory
//@pUserKey as uniqueidentifier = NULL,
//@pCustKey as uniqueidentifier = NULL,
//@pStartDate as varchar(30) = NULL,
//@pEndDate as varchar(30) = NULL
/// </summary>
private void GetOrderSummary(Guid? customerKey)
{
//TODO get order summary from database
//TODO add index field to query so it can be sorted easily
_orderSummary = new SortedList<Int64, Order>();
Order order;
SqlService sql = new SqlService(_sqlConnection);
//TODO Get from logged in user
sql.AddParameter("@pCustKey", System.Data.SqlDbType.UniqueIdentifier, customerKey);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetCustHistory"))
{
if (reader.HasRows)
{
while (reader.Read())
{
order = new Order();
order.IndentNo = reader.SafeGetInt64(reader.GetOrdinal("indentNo")).Value;
order.InvoiceNo = reader.SafeGetString(reader.GetOrdinal("invoiceNo"));
//order.OrderKey = reader.SafeGetGuid(reader.GetOrdinal("orderKey")).Value;
order.Date = reader.SafeGetString(reader.GetOrdinal("orderDate"));
order.OrderNo = reader.SafeGetString(reader.GetOrdinal("orderNo"));
order.Status = reader.SafeGetString(reader.GetOrdinal("orderStatus"));
order.TTNo = reader.SafeGetString(reader.GetOrdinal("orderTTNo"));
order.TTOutTime = reader.SafeGetString(reader.GetOrdinal("orderTTOut"));
order.CreatedBy = reader.SafeGetString(reader.GetOrdinal("orderedBy"));
_orderSummary.Add(order.IndentNo, order);
}
}
}
}
public RecipientGroup GetRecipientGroup(Guid id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
RecipientGroup r = new RecipientGroup();
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetSMSRecipientGroup"))
{
if (reader.HasRows)
{
while (reader.Read())
{
r.Id = id;
r.GroupName = reader.SafeGetString(reader.GetOrdinal("GroupName"));
r.UserRole = reader.SafeGetString(reader.GetOrdinal("UserRole"));
}
}
}
return r;
}
private void CreateUpdateGroups(RecipientGroup group)
{
//ICMS_spCreateUpdateRecipientGroup
// @pId as uniqueidentifier = null,
//@pGroupName as nvarchar(50),
//@pRoleName as varchar(20)
SqlService sql = new SqlService(_sqlConnection);
if (group.Id != null)
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, group.Id);
sql.AddParameter("@pGroupName", System.Data.SqlDbType.VarChar, group.GroupName);
sql.AddParameter("@pRoleName", System.Data.SqlDbType.VarChar, group.UserRole);
sql.ExecuteSP("ICMS_spCreateSMSUpdateRecipientGroup");
}
public void DeleteUser(string id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.NVarChar, id);
sql.ExecuteSP("ICMS_spDeleteUser");
}
private void CreateUpdateSalesArea(SalesArea s)
{
SqlService sql = new SqlService(_sqlConnection);
if (s.Id != null)
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, s.Id);
sql.AddParameter("@pSalesAreaName", System.Data.SqlDbType.VarChar, s.SalesAreaName);
sql.AddParameter("@pOrderCode", System.Data.SqlDbType.VarChar, s.OrderCode);
sql.ExecuteSP("ICMS_spCreateUpdateSalesArea");
}
/// <summary>
///
/// </summary>
public void LogMessageId()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pMessageId", System.Data.SqlDbType.VarChar, _messageId);
sql.AddParameter("@pMsgDateTime", System.Data.SqlDbType.DateTime, _messageDateTime);
try
{
sql.ExecuteSP("MMD_spLogMessageId");
}
catch (Exception ex)
{
throw new Exception("LogMessageId: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
finally
{
}
}
public void DeleteDriver(Guid id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pDriverKey", System.Data.SqlDbType.UniqueIdentifier, id);
sql.ExecuteSP("ICMS_spDeleteDriver");
}
