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);
}
}
}
}
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;
}
}
}
}
//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");
}
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");
}
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");
}
public Inventory Get(int id)
{
var service = new SqlService(ConfigurationManager.ConnectionStrings["PrimarySql"].ConnectionString);
var inventory = service.GetInventorybySku(id);
if (inventory == null)
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
return(inventory);
}
/// <summary>
/// 作废
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnBatchDelete_Click(object sender, EventArgs e)
{
IEnumerable <string> selectIds = GetSelectIds();
try
{
Log(string.Format(@"作废单据号:{0}成功。", selectIds));
//
foreach (var ids in selectIds)
{
var status = new LHBillStatus();
status.FCompanyId = CurrentUser.AccountComId;
status.FDeptId = CurrentUser.AccountOrgId;
status.FDate = DateTime.Now;
status.FOperator = CurrentUser.AccountName;
status.FActionName = EnumDescription.GetFieldText(GasEnumBillStauts.Voided);
status.KeyId = ids;
status.FMemo = string.Format("单据号{0}被{1}作废处理。", ids, CurrentUser.AccountName);
GasHelper.AddBillStatus(status);
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@KeyId", ids);
parms.Add("@companyId", CurrentUser.AccountComId);
parms.Add("@Bill", 1);
SqlService.ExecuteProcedureNonQuery("proc_DeleteFlag", parms);
}
StockOutService.Update(p => p.FCompanyId == CurrentUser.AccountComId && selectIds.Contains(p.KeyId), p => new LHStockOut
{
FFlag = 0, //
FStatus = Convert.ToInt32(GasEnumBillStauts.Voided), //
FProgress = Convert.ToInt32(GasEnumBillStauts.Voided)
});
//PassCardService.Update(p =>p.FCompanyId == CurrentUser.AccountComId && selectIds.Contains(p.KeyId), p => new LHPassCard
//{
// FFlag = 0, //
// FStatus = Convert.ToInt32(GasEnumBillStauts.Add), //
// FProgress = Convert.ToInt32(GasEnumBillStauts.Add)
//});
Alert.Show("作废成功！", MessageBoxIcon.Information);
BindDataGrid();
}
catch (Exception)
{
Alert.Show("作废失败！", MessageBoxIcon.Warning);
}
}
private void ItemMenu_Click(object sender, EventArgs e)
{
var FormName = SqlService.GetDataTable("select FormName from MenuApp where ID =" + ((ToolStripItem)sender).Name).Rows[0][0].ToString();
if (FormName != "")
{
var frm = new Form();
frm = CreateObjectInstance(FormName);
frm.Name = FormName;
ShowFormToTabPage(frm);
}
}
/// <summary>
/// 绑定数据表格
/// </summary>
private void BindDataGrid()
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@FDate", dpkFDate.SelectedDate);
parms.Add("@FCCCode", txtFCode.Text.Trim());
parms.Add("@companyid", ddlCompany.SelectedValue);
if (!string.IsNullOrEmpty(ddlSeller.SelectedText))
{
parms.Add("@Seller", ddlSeller.SelectedValue);
}
var list = SqlService.ExecuteProcedureCommand("proc_CustomerMonthProc", parms).Tables[0];
if (list != null && list.Rows.Count > 0)
{
//绑定数据源
Grid1.DataSource = list;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FCode", "合计" },
{ "FInitAR", list.Compute("sum(FInitAR)", "true").ToString() },
{ "FPeriodAR", list.Compute("sum(FPeriodAR)", "true").ToString() },
{ "FPeriodReturn", list.Compute("sum(FPeriodReturn)", "true").ToString() },
{ "FPeriodRecover", list.Compute("sum(FPeriodRecover)", "true").ToString() },
{ "FFinalAR", list.Compute("sum(FFinalAR)", "true").ToString() },
{ "FDiscountAmount", list.Compute("sum(FDiscountAmount)", "true").ToString() },
};
Grid1.SummaryData = summary;
}
else
{
Grid1.DataSource = null;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FCode", "合计" },
{ "FInitAR", "0.00" },
{ "FPeriodAR", "0.00" },
{ "FPeriodReturn", "0.00" },
{ "FPeriodRecover", "0.00" },
{ "FFinalAR", "0.00" },
};
Grid1.SummaryData = summary;
}
}
public DataSet GetAllShows()
{
SqlService sql = new SqlService();
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.Add("@Lang", SqlDbType.NVarChar).Value = Lang;
sqlcomm.CommandText = "[dbo].[sp_Get_Show_By_Language]";
DataSet DS = sql.ExecuteSPDataSet(sqlcomm);
return(DS);
}
private async Task MainAsync()
{
if (!File.Exists(".env"))
{
File.Copy(".env.example", ".env");
Console.WriteLine(".env file created. Please configure and restart.");
return;
}
DotEnv.Config(false);
_client = new DiscordSocketClient(new DiscordSocketConfig
{
LogLevel = LogSeverity.Verbose,
MessageCacheSize = 100
});
_client.Log += Log;
await _client.LoginAsync(TokenType.Bot, Environment.GetEnvironmentVariable("DISCORD_TOKEN"));
await _client.StartAsync();
var connStr = $"server={Environment.GetEnvironmentVariable("MYSQL_SERVER")};" +
$"user={Environment.GetEnvironmentVariable("MYSQL_USER")};" +
$"password={Environment.GetEnvironmentVariable("MYSQL_PASSWORD")};" +
$"database={Environment.GetEnvironmentVariable("MYSQL_DATABASE")};" +
$"port={Environment.GetEnvironmentVariable("MYSQL_PORT")}";
_sqlService = new SqlService(connStr, _client);
_services = new ServiceCollection()
.AddSingleton(_client)
.AddSingleton(_sqlService)
.BuildServiceProvider();
_commands = new CommandService();
await _commands.AddModulesAsync(Assembly.GetEntryAssembly(), _services);
PlayerList = _sqlService.QueryPlayers();
ItemMap = _sqlService.QueryItems();
_client.MessageUpdated += MessageUpdated;
_client.MessageReceived += HandleCommandAsync;
_client.Disconnected += HandleDisconnect;
Enemies.AddRange(Enemy.CreatePerPlayer(PlayerList.Count * 3));
Enemies = Enemies.OrderBy(en => en.GetLevel()).ToList();
var timerService = new TimerService(int.Parse(Environment.GetEnvironmentVariable("EXP_SECONDS")),
int.Parse(Environment.GetEnvironmentVariable("ENEMY_REFRESH_SECONDS")), _sqlService);
await Task.Delay(-1);
}
public override async Task <JobResult> Execute(SqlService sql, AppSettings settings)
{
int retries = 0;
TOP:
var file = await sql.UploadedFileById(FileId);
if (settings.BunnyCDN_User == "TEST" && settings.BunnyCDN_Password == "TEST")
{
return(JobResult.Success());
}
using (var client = new FtpClient("storage.bunnycdn.com"))
{
client.Credentials = new NetworkCredential(settings.BunnyCDN_User, settings.BunnyCDN_Password);
await client.ConnectAsync();
using (var stream = File.OpenRead(Path.Combine("public", "files", file.MungedName)))
{
try
{
await client.UploadAsync(stream, file.MungedName, progress : new Progress((RelativePath)file.MungedName));
}
catch (Exception ex)
{
if (retries > 10)
{
throw;
}
Utils.Log(ex.ToString());
Utils.Log("Retrying FTP Upload");
retries++;
goto TOP;
}
}
await sql.EnqueueJob(new Job
{
Priority = Job.JobPriority.High,
Payload = new IndexJob
{
Archive = new Archive(new HTTPDownloader.State(file.Uri))
{
Name = file.MungedName,
Size = file.Size,
Hash = file.Hash,
}
}
});
}
return(JobResult.Success());
}
public override async Task <JobResult> Execute(DBContext db, SqlService sql, AppSettings settings)
{
using (var queue = new WorkQueue())
{
var files = Directory.EnumerateFiles(settings.ArchiveDir)
.Where(f => !f.EndsWith(Consts.HashFileExtension))
.ToList();
var total_count = files.Count;
int completed = 0;
await files.PMap(queue, async file =>
{
try
{
Interlocked.Increment(ref completed);
if (await sql.HaveIndexdFile(await file.FileHashCachedAsync()))
{
Utils.Log($"({completed}/{total_count}) Skipping {Path.GetFileName(file)}, it's already indexed");
return;
}
var sub_folder = Guid.NewGuid().ToString();
string folder = Path.Combine(settings.DownloadDir, sub_folder);
Utils.Log($"({completed}/{total_count}) Copying {file}");
Directory.CreateDirectory(folder);
Utils.Log($"({completed}/{total_count}) Copying {file}");
File.Copy(file, Path.Combine(folder, Path.GetFileName(file)));
Utils.Log($"({completed}/{total_count}) Analyzing {file}");
var vfs = new Context(queue, true);
await vfs.AddRoot(folder);
var root = vfs.Index.ByRootPath.First().Value;
Utils.Log($"({completed}/{total_count}) Ingesting {root.ThisAndAllChildren.Count()} files");
await sql.MergeVirtualFile(root);
Utils.Log($"({completed}/{total_count}) Cleaning up {file}");
Utils.DeleteDirectory(folder);
}
catch (Exception ex)
{
Utils.Log(ex.ToString());
}
});
}
return(JobResult.Success());
}
/// <summary>
/// 作废
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnBatchDelete_Click(object sender, EventArgs e)
{
IEnumerable <string> selectIds = GetSelectIds();
try
{
Log(string.Format(@"作废单据号:{0}成功。", selectIds));
using (var trans = new TransactionScope())
{
//
foreach (var ids in selectIds)
{
var status = new LHBillStatus();
status.FCompanyId = CurrentUser.AccountComId;
status.FDeptId = CurrentUser.AccountOrgId;
status.FDate = DateTime.Now;
status.FOperator = CurrentUser.AccountName;
status.FActionName = EnumDescription.GetFieldText(GasEnumBillStauts.Voided);
status.KeyId = ids;
status.FMemo = string.Format("单据号{0}被{1}作废处理。", ids, CurrentUser.AccountName);
GasHelper.AddBillStatus(status);
}
LeaseService.Update(p => selectIds.Contains(p.KeyId), p => new LHLease
{
FFlag = 0, //
FStatus = Convert.ToInt32(GasEnumBillStauts.Voided), //
FProgress = Convert.ToInt32(GasEnumBillStauts.Voided)
});
foreach (var ids in selectIds)
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@keyID", ids);
parms.Add("@companyId", CurrentUser.AccountComId);
SqlService.ExecuteProcedureCommand("proc_LeaseKillSK", parms);
}
trans.Complete();
}
Alert.Show("作废成功！", MessageBoxIcon.Information);
BindDataGrid();
}
catch (Exception)
{
Alert.Show("作废失败！", MessageBoxIcon.Warning);
}
}
/// <summary>
/// 绑定数据表格
/// </summary>
private void BindDataGrid()
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@FCompanyId", CurrentUser.AccountComId);
parms.Add("@FBegin", Convert.ToDateTime(dpFDateBegion.SelectedDate).ToString("yyyy-MM-dd"));
parms.Add("@FEnd", Convert.ToDateTime(dpFDateEnd.SelectedDate).ToString("yyyy-MM-dd"));
parms.Add("@FCode", txtFCode.Text.Trim());
parms.Add("@FItemCode", txtItemCode.Text.Trim());
parms.Add("@FItemName", txtItemName.Text.Trim());
parms.Add("@KeyId", txtKeyId.Text.Trim());
parms.Add("@FClass", ddlFClass.SelectedValue);
parms.Add("@FVehicleNum", ddlFVehicleNum.SelectedValue);
//
var list = SqlService.ExecuteProcedureCommand("rpt_LiquidPurchase", parms).Tables[0];
if (list != null && list.Rows.Count > 0)
{
Grid1.DataSource = list;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FCode", "合计" },
{ "FQty", list.Compute("sum(FQty)", "true").ToString() },
{ "FAmount", list.Compute("sum(FAmount)", "true").ToString() },
{ "FBottleQty", list.Compute("sum(FBottleQty)", "true").ToString() },
{ "FRecycleQty", list.Compute("sum(FRecycleQty)", "true").ToString() },
};
Grid1.SummaryData = summary;
}
else
{
Grid1.DataSource = null;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FCode", "合计" },
{ "FQty", "0" },
{ "FAmount", "0.00" },
{ "FBottleQty", "0.00" },
{ "FRecycleQty", "0.00" },
};
Grid1.SummaryData = summary;
}
}
public List <TC_Reason_Type> Get_TC_Reason_Type()
{
List <TC_Reason_Type> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_TC_Reason_Type"))
{
result = sqlDataReader.MapToList <TC_Reason_Type>();
}
}
return(result);
}
public List <TransportRouteModel> GetTransportRoute()
{
List <TransportRouteModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetTransportRoute"))
{
result = sqlDataReader.MapToList <TransportRouteModel>();
}
}
return(result);
}
public string Get_Fee_Generated_Current_Month(int Academic_Year)
{
string text = string.Empty;
string result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Academic_Year", Academic_Year);
text = sqlService.ExecuteSPScalar("dbo.USP_Get_Fee_Generated_Month");
result = text;
}
return(result);
}
void _startStatusOption()
{
SqlCommand sqlCommand_ = new SqlCommand();
StatusOptionSelectB statusOptionSelectB_ = new StatusOptionSelectB();
sqlCommand_._addHeadstream(statusOptionSelectB_);
SqlService sqlService_ = __singleton <SqlService> ._instance();
if (sqlService_._runSqlCommand(sqlCommand_, statusOptionSelectB_))
{
statusOptionSelectB_._runInit();
}
}
public virtual async Task <string> FirstOrDefaultById <T>(T id)
{
var idString = id.ToString();
Guid GuidType = Guid.Empty;
if (Guid.TryParse(idString, out GuidType))
{
idString = $"'{GuidType.ToString()}'";
}
var result = await SqlService.GetDataResult(_spNames.FirstOrDefaultById + " " + idString);
return(result);
}
public List <ClassTypeModel> GetClassDetails()
{
List <ClassTypeModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetClassType"))
{
result = sqlDataReader.MapToList <ClassTypeModel>();
}
}
return(result);
}
void CheckedCheckBox(int e)
{
var check = SqlService.GetDataTable("select * from UserRole where id_user ='******' and id_role='" + dgv.Rows[e].Cells[0].Value + "'");
if (check.Rows.Count == 0)
{
SqlService.ExecuteQuery("insert into UserRole (id_user,id_role,allow_view,allow_add,allow_update,allow_delete,allow_print,allow_custom_access) values('" + TextBoxUser.Tag.ToString() + "','" + dgv.Rows[e].Cells[0].Value + "','" + dgv.Rows[e].Cells[3].Value + "','" + dgv.Rows[e].Cells[4].Value + "','" + dgv.Rows[e].Cells[5].Value + "','" + dgv.Rows[e].Cells[6].Value + "','" + dgv.Rows[e].Cells[7].Value + "','" + dgv.Rows[e].Cells[8].Value + "')");
}
else
{
SqlService.ExecuteQuery("update UserRole set allow_view='" + dgv.Rows[e].Cells[3].Value + "', allow_add='" + dgv.Rows[e].Cells[4].Value + "',allow_update='" + dgv.Rows[e].Cells[5].Value + "',allow_delete='" + dgv.Rows[e].Cells[6].Value + "',allow_print='" + dgv.Rows[e].Cells[7].Value + "',allow_custom_access='" + dgv.Rows[e].Cells[8].Value + "' where id_user='******' and id_role='" + dgv.Rows[e].Cells[0].Value + "'");
}
}
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 List <FeeTypeModel> GetLostableFeeType()
{
List <FeeTypeModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_GetPurchasableFeeType"))
{
result = sqlDataReader.MapToList <FeeTypeModel>();
}
}
return(result);
}
/// <summary>
/// 绑定数据表格
/// </summary>
private void BindDataGrid()
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@FCompanyId", CurrentUser.AccountComId);
parms.Add("@FDate", Convert.ToDateTime(dateBegin.SelectedDate).ToString("yyyy-MM-dd"));
if (!string.IsNullOrWhiteSpace(ddlType.SelectedText.Trim()))
{
parms.Add("@FCateId", ddlType.SelectedValue);
}
parms.Add("@FName", txtFName.Text.Trim());
var list = SqlService.ExecuteProcedureCommand("rpt_Inventory", parms).Tables[0];
if (list != null && list.Rows.Count > 0)
{
//绑定数据源
Grid1.DataSource = list;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FName", "合计" },
{ "FInQty", list.Compute("sum(FInQty)", "true").ToString() },
{ "FOutQty", list.Compute("sum(FOutQty)", "true").ToString() },
{ "FInitQty", list.Compute("sum(FInitQty)", "true").ToString() },
{ "FEndQty", list.Compute("sum(FEndQty)", "true").ToString() },
};
Grid1.SummaryData = summary;
}
else
{
//合计
var summary = new JObject
{
{ "FName", "合计" },
{ "FInQty", "0.00" },
{ "FOutQty", "0.00" },
{ "FInitQty", "0.00" },
{ "FNowQty", "0.00" },
};
Grid1.SummaryData = summary;
}
}
public override string GetSpParams()
{
var result = "";
result += SqlService.Convert(Skip) + ", ";
result += SqlService.Convert(Size) + ", ";
result += SqlService.Convert(Id) + ", ";
result += SqlService.Convert(MultiText_Id) + ", ";
result += SqlService.Convert(MinId) + ", ";
result += SqlService.Convert(MaxId) + ", ";
result += SqlService.Convert(Username) + ", ";
result += SqlService.Convert(MultiText_Username) + ", ";
result += SqlService.Convert(Password) + ", ";
result += SqlService.Convert(MultiText_Password) + ", ";
result += SqlService.Convert(UserInfoId) + ", ";
result += SqlService.Convert(MultiText_UserInfoId) + ", ";
result += SqlService.Convert(MinUserInfoId) + ", ";
result += SqlService.Convert(MaxUserInfoId) + ", ";
result += SqlService.Convert(RoleId) + ", ";
result += SqlService.Convert(MultiText_RoleId) + ", ";
result += SqlService.Convert(MinRoleId) + ", ";
result += SqlService.Convert(MaxRoleId) + ", ";
result += SqlService.Convert(IsDeleted) + ", ";
result += SqlService.Convert(MultiText_IsDeleted) + ", ";
result += SqlService.Convert(ModifyDate) + ", ";
result += SqlService.Convert(MultiText_ModifyDate) + ", ";
result += SqlService.Convert(MinModifyDate) + ", ";
result += SqlService.Convert(MaxModifyDate) + ", ";
result += SqlService.Convert(SubmitDate) + ", ";
result += SqlService.Convert(MultiText_SubmitDate) + ", ";
result += SqlService.Convert(MinSubmitDate) + ", ";
result += SqlService.Convert(MaxSubmitDate) + " ";
return(result);
}
public override string GetSpParams()
{
var result = "";
result += SqlService.Convert(Skip) + ", ";
result += SqlService.Convert(Size) + ", ";
result += SqlService.Convert(Id) + ", ";
result += SqlService.Convert(MultiText_Id) + ", ";
result += SqlService.Convert(MinId) + ", ";
result += SqlService.Convert(MaxId) + ", ";
result += SqlService.Convert(PhoneNumber) + ", ";
result += SqlService.Convert(MultiText_PhoneNumber) + ", ";
result += SqlService.Convert(ZipCode) + ", ";
result += SqlService.Convert(MultiText_ZipCode) + ", ";
result += SqlService.Convert(Address1) + ", ";
result += SqlService.Convert(MultiText_Address1) + ", ";
result += SqlService.Convert(Address2) + ", ";
result += SqlService.Convert(MultiText_Address2) + ", ";
result += SqlService.Convert(IsDeleted) + ", ";
result += SqlService.Convert(MultiText_IsDeleted) + ", ";
result += SqlService.Convert(ModifyDate) + ", ";
result += SqlService.Convert(MultiText_ModifyDate) + ", ";
result += SqlService.Convert(MinModifyDate) + ", ";
result += SqlService.Convert(MaxModifyDate) + ", ";
result += SqlService.Convert(SubmitDate) + ", ";
result += SqlService.Convert(MultiText_SubmitDate) + ", ";
result += SqlService.Convert(MinSubmitDate) + ", ";
result += SqlService.Convert(MaxSubmitDate) + " ";
return(result);
}
/// <summary>
/// 提交月结
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
var company = CompanyService.FirstOrDefault(p => p.id == CurrentUser.AccountComId);
if (company != null)
{
DateTime monthbegin;
DateTime monthend;
if (company.FMonthly.Contains("是"))
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@Fdate", Convert.ToDateTime(dpkFMonth.SelectedDate).ToString("yyyy-MM-dd"));
var date = SqlService.ExecuteProcedureCommand("proc_MonthDay", parms);
monthbegin = Convert.ToDateTime(date.Tables[0].Rows[0][0]);
monthend = Convert.ToDateTime(date.Tables[1].Rows[0][0]);
}
else
{
monthbegin = Convert.ToDateTime(Convert.ToDateTime(dpkFMonth.SelectedDate).AddMonths(-1).ToString("yyyy-MM") + "-" + company.FMonthlyDay).AddDays(1);
monthend = Convert.ToDateTime(Convert.ToDateTime(dpkFMonth.SelectedDate).ToString("yyyy-MM") + "-" + company.FMonthlyDay);
}
//--------------------------------
var pamrs = new Dictionary <string, object>();
pamrs.Clear();
pamrs.Add("@companyId", CurrentUser.AccountComId);
pamrs.Add("@FMonth", Convert.ToDateTime(dpkFMonth.SelectedDate).ToString("yyyy-MM"));
pamrs.Add("@FMonthT", Convert.ToDateTime(dpkFMonth.SelectedDate).AddMonths(1).ToString("yyyy-MM"));
pamrs.Add("@CreateBy", CurrentUser.AccountName);
pamrs.Add("@begin", monthbegin.ToString("yyyy-MM-dd"));
pamrs.Add("@end", monthend.ToString("yyyy-MM-dd"));
SqlService.ExecuteProcedureNonQuery("proc_Month", pamrs);
Alert.Show(string.Format("【{0}～{1}】月结完成", monthbegin.ToString("yyyy-MM-dd"), monthend.ToString("yyyy-MM-dd")), MessageBoxIcon.Information);
}
}
catch (Exception)
{
Alert.Show("月结失败", MessageBoxIcon.Warning);
}
}
/// <summary>
/// 绑定数据表格
/// </summary>
private void BindDataGrid()
{
var parms = new Dictionary <string, object>();
parms.Clear();
parms.Add("@begin", MonthFirstDay(Convert.ToDateTime(dpkFDate.SelectedDate)));
parms.Add("@end", MonthEnd(Convert.ToDateTime(dpkFDate.SelectedDate)));
parms.Add("@FCode", txtFCode.Text);
parms.Add("@FBottle", "");
parms.Add("@companyid", ddlCompany.SelectedValue);
var list = SqlService.ExecuteProcedureCommand("proc_UnitAllBottle", parms).Tables[0];
if (list != null && list.Rows.Count > 0)
{
//绑定数据源
Grid1.DataSource = list;
Grid1.DataBind();
//合计
var summary = new JObject
{
{ "FCode", "合计" },
//{"FInitAR", list.Compute("sum(FInitAR)","true").ToString()},
//{"FPeriodAR", list.Compute("sum(FPeriodAR)","true").ToString()},
//{"FPeriodReturn", list.Compute("sum(FPeriodReturn)","true").ToString()},
//{"FPeriodRecover", list.Compute("sum(FPeriodRecover)","true").ToString()},
{ "FBottleQty", list.Compute("sum(FBottleQty)", "true").ToString() },
};
Grid1.SummaryData = summary;
}
else
{
//合计
var summary = new JObject
{
{ "FCode", "合计" },
{ "FInitAR", "0.00" },
{ "FPeriodAR", "0.00" },
{ "FPeriodReturn", "0.00" },
{ "FPeriodRecover", "0.00" },
{ "FFinalAR", "0.00" },
};
Grid1.SummaryData = summary;
}
}
public override string GetSpParams()
{
var result = "";
result += SqlService.Convert(Skip) + ", ";
result += SqlService.Convert(Size) + ", ";
result += SqlService.Convert(Id) + ", ";
result += SqlService.Convert(MultiText_Id) + ", ";
result += SqlService.Convert(MinId) + ", ";
result += SqlService.Convert(MaxId) + ", ";
result += SqlService.Convert(ShippingTypeId) + ", ";
result += SqlService.Convert(MultiText_ShippingTypeId) + ", ";
result += SqlService.Convert(MinShippingTypeId) + ", ";
result += SqlService.Convert(MaxShippingTypeId) + ", ";
result += SqlService.Convert(ShippingPrice) + ", ";
result += SqlService.Convert(MultiText_ShippingPrice) + ", ";
result += SqlService.Convert(MinShippingPrice) + ", ";
result += SqlService.Convert(MaxShippingPrice) + ", ";
result += SqlService.Convert(TotalPrice) + ", ";
result += SqlService.Convert(MultiText_TotalPrice) + ", ";
result += SqlService.Convert(MinTotalPrice) + ", ";
result += SqlService.Convert(MaxTotalPrice) + ", ";
result += SqlService.Convert(FactorInfoId) + ", ";
result += SqlService.Convert(MultiText_FactorInfoId) + ", ";
result += SqlService.Convert(MinFactorInfoId) + ", ";
result += SqlService.Convert(MaxFactorInfoId) + ", ";
result += SqlService.Convert(IsDeleted) + ", ";
result += SqlService.Convert(MultiText_IsDeleted) + ", ";
result += SqlService.Convert(ModifyDate) + ", ";
result += SqlService.Convert(MultiText_ModifyDate) + ", ";
result += SqlService.Convert(MinModifyDate) + ", ";
result += SqlService.Convert(MaxModifyDate) + ", ";
result += SqlService.Convert(SubmitDate) + ", ";
result += SqlService.Convert(MultiText_SubmitDate) + ", ";
result += SqlService.Convert(MinSubmitDate) + ", ";
result += SqlService.Convert(MaxSubmitDate) + " ";
return(result);
}
public DataSet GetDailyFeeCollection(DateTime collectionDate)
{
DataSet result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@CollectionDate", collectionDate);
using (DataSet dataSet = sqlService.ExecuteSPDataSet("dbo.USP_GetDailyFeeCollection"))
{
result = dataSet;
}
}
return(result);
}
public List <MonthlyFeeGenerateModel> Get_Student_Previous_Dues_List(long Registration_No)
{
List <MonthlyFeeGenerateModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Registration_No", SqlDbType.BigInt, Registration_No);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Previous_Dues_List"))
{
result = sqlDataReader.MapToList <MonthlyFeeGenerateModel>();
}
}
return(result);
}
public List <MonthlyFeeDepositModel> Get_Student_Deposited_List(long Student_Id)
{
List <MonthlyFeeDepositModel> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Registration_No", SqlDbType.BigInt, Student_Id);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Fee_Deposited_List"))
{
result = sqlDataReader.MapToList <MonthlyFeeDepositModel>();
}
}
return(result);
}
public List <Fee_Deposit_By_Date_View_Model> Get_Fee_Deposit_By_Date(DateTime Fee_Deposit_Date)
{
List <Fee_Deposit_By_Date_View_Model> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Fee_Deposit_Date", SqlDbType.Date, Fee_Deposit_Date);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Fee_Deposit_By_Date"))
{
result = sqlDataReader.MapToList <Fee_Deposit_By_Date_View_Model>();
}
}
return(result);
}
public List <Student_Fee_Info_Type> Get_Student_Fee_Info_Type(bool Is_Display_All)
{
List <Student_Fee_Info_Type> result;
using (SqlService sqlService = new SqlService(ConnectionString.ConnectionStrings))
{
sqlService.AddParameter("@Is_Display_All", SqlDbType.Bit, Is_Display_All);
using (SqlDataReader sqlDataReader = sqlService.ExecuteSPReader("dbo.USP_Get_Student_Fee_Info_Type"))
{
result = sqlDataReader.MapToList <Student_Fee_Info_Type>();
}
}
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);
}
}
private void CreateUpdateContractor(Contractor contractor)
{
SqlService sql = new SqlService(_sqlConnection);
if (contractor.Id != null)
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, contractor.Id);
sql.AddParameter("@pName", System.Data.SqlDbType.VarChar, contractor.Name);
sql.AddParameter("@pRef", System.Data.SqlDbType.VarChar, contractor.Ref);
sql.AddParameter("@pMobile", System.Data.SqlDbType.BigInt, contractor.Mobile);
sql.AddParameter("@pSalesArea", System.Data.SqlDbType.VarChar, contractor.SalesAreaName);
if (contractor.TravelDistance != null)
sql.AddParameter("@pTravelDistance", System.Data.SqlDbType.Int, contractor.TravelDistance);
if (contractor.TravelTime != null)
sql.AddParameter("@pTravelTime", System.Data.SqlDbType.Int, contractor.TravelTime);
//sql.AddParameter("@", System.Data.SqlDbType,);
sql.ExecuteSP("ICMS_spCreateUpdateContractor");
}
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 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 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 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;
}
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 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 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");
}
public void DeleteUser(string id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.NVarChar, id);
sql.ExecuteSP("ICMS_spDeleteUser");
}
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.";
}
}
/// <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);
}
}
}
}
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");
}
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 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");
}
public void DeleteDriver(Guid id)
{
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pDriverKey", System.Data.SqlDbType.UniqueIdentifier, id);
sql.ExecuteSP("ICMS_spDeleteDriver");
}
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;
}
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 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;
}
/// <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
{
}
}
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");
}
private IEnumerable<ListIdentity> GetIdentitiesList()
{
IList<ListIdentity> list = new List<ListIdentity>();
var sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlService sql = new SqlService(sqlConnectionString);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetBioIdentitiesList"))
{
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(new ListIdentity
{
Name = reader.GetString(reader.GetOrdinal("Name")),
PONo = reader.GetString(reader.GetOrdinal("PONo")),
CompanyName = reader.GetString(reader.GetOrdinal("CompanyName")),
ContractorName = reader.GetString(reader.GetOrdinal("ContractorName")),
RegistrationId = reader.GetGuid(reader.GetOrdinal("RegistrationId")),
IdentityType = reader.GetString(reader.GetOrdinal("IdentityType"))
});
}
}
}
return list;
}
