private int GetWorkingId(int workId, int workerId)
{
StringBuilder query = new StringBuilder();
var parameters = new List <SqlParameter>();
int id = 0;
query.AppendLine("select top 1");
query.AppendLine(" [id]");
query.AppendLine("from");
query.AppendLine(" [KGWS].[dbo].[Working]");
query.AppendLine("where");
query.AppendLine(" [workId]=@workId");
query.AppendLine("and");
query.AppendLine(" [workerId]=@workerId");
query.AppendLine("and");
query.AppendLine(" [isDone]='false'");
query.AppendLine("order by [workOpId] ");
parameters.Clear();
parameters.Add(new SqlParameter("@workId", workId));
parameters.Add(new SqlParameter("@workerId", workerId));
SQLServerHelper.Query(
query.ToString(),
parameters.ToArray(),
reader =>
{
id = Convert.ToInt32(reader["id"]);
});
return(id);
}
private bool IsExistsPalette(PaletteEntity palette)
{
var parameters = new List <SqlParameter>();
bool IsExistsPalette = false;
int paletteCount = 0;
var sql = @"
select
count([palette_id]) as palette_id
from [KGWS].[dbo].[palettes]
where
[palette_id]=@palette_id
";
parameters.Clear();
parameters.Add(new SqlParameter("@palette_id", palette.PaletteId));
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
paletteCount = Convert.ToInt32(reader["palette_id"]);
});
if (paletteCount == 0)
{
IsExistsPalette = false;
}
else
{
IsExistsPalette = true;
}
return(IsExistsPalette);
}
public List <BomPartsEntity> GetBomParts(string assyNumber)
{
var parameters = new List <SqlParameter>();
var list = new List <BomPartsEntity>();
var sql = @"
select
bom.[assy_number]
,bom.[parts_number]
,bom.[parts_name]
,bom.[parts_quantity]
,isnull(details_sum.[parts_quantity],0) as details_sum_parts_quantity
from [KGWS].[dbo].[bom] bom
left join (
select
details.[parts_number]
,sum(details.[parts_quantity]) as parts_quantity
from [KGWS].[dbo].[palette_details] details
where
details.[is_deleted]=@is_deleted
and
details.[assy_number]=@assy_number
group by details.[parts_number]
) as details_sum
on bom.[parts_number]=details_sum.[parts_number]
where
bom.[assy_number]=@assy_number
";
parameters.Clear();
parameters.Add(new SqlParameter("@assy_number", assyNumber));
parameters.Add(new SqlParameter("@is_deleted", false));
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
list.Add(new BomPartsEntity(
Convert.ToString(reader["assy_number"]),
Convert.ToString(reader["parts_number"]),
Convert.ToString(reader["parts_name"]),
Convert.ToInt32(reader["parts_quantity"]),
Convert.ToInt32(reader["details_sum_parts_quantity"])));
});
return(list);
}
public List <WorkingEntity> GetWorkingData()
{
StringBuilder query = new StringBuilder();
var parameters = new List <SqlParameter>();
var list = new List <WorkingEntity>();
query.AppendLine("select");
query.AppendLine(" [id]");
query.AppendLine(" ,[workTitleId]");
query.AppendLine(" ,[workerId]");
query.AppendLine(" ,[workOpId]");
query.AppendLine(" ,[workTitle]");
query.AppendLine(" ,[workOpName]");
query.AppendLine(" ,[workDetails]");
query.AppendLine(" ,[workDetailsId]");
query.AppendLine(" ,[caution]");
query.AppendLine(" ,[isDone]");
query.AppendLine(" ,[isSync]");
query.AppendLine(" ,[isCurrent]");
query.AppendLine("from");
query.AppendLine(" [KGWS].[dbo].[Working]");
parameters.Clear();
SQLServerHelper.Query(
query.ToString(),
parameters.ToArray(),
reader =>
{
list.Add(new WorkingEntity(
Convert.ToInt32(reader["id"]),
Convert.ToInt32(reader["workTitleId"]),
Convert.ToInt32(reader["workerId"]),
Convert.ToInt32(reader["workOpId"]),
Convert.ToString(reader["workTitle"]),
Convert.ToString(reader["workOpName"]),
Convert.ToString(reader["workDetails"]),
Convert.ToInt32(reader["workDetailsId"]),
Convert.ToString(reader["caution"]),
Convert.ToBoolean(reader["isDone"]),
Convert.ToBoolean(reader["isSync"]),
Convert.ToBoolean(reader["isCurrent"])));
});
return(list);
}
public List <PaletteDetailsEntity> GetPaletteDetails(int paletteid)
{
var parameters = new List <SqlParameter>();
var list = new List <PaletteDetailsEntity>();
var sql = @"
select
[palette_details_id]
,[palette_id]
,[assy_number]
,[parts_number]
,[parts_name]
,[parts_quantity]
,[place_key]
,[subAssembly_key]
,[is_deleted]
from [KGWS].[dbo].[palette_details]
where
[palette_id]=@palette_id
and
[is_deleted]=@is_deleted
order by [parts_number]
";
parameters.Clear();
parameters.Add(new SqlParameter("@palette_id", paletteid));
parameters.Add(new SqlParameter("@is_deleted", false));
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
list.Add(new PaletteDetailsEntity(
Convert.ToInt32(reader["palette_id"]),
Convert.ToInt32(reader["palette_details_id"]),
Convert.ToString(reader["assy_number"]),
Convert.ToString(reader["parts_number"]),
Convert.ToString(reader["parts_name"]),
Convert.ToInt32(reader["parts_quantity"]),
Convert.ToString(reader["place_key"]),
Convert.ToString(reader["subAssembly_key"]),
Convert.ToBoolean(reader["is_deleted"])));
});
return(list);
}
public List <WorkEntity> GetWorks()
{
var parameters = new List <SqlParameter>();
var list = new List <WorkEntity>();
var sql = @"
select
block.[work_block_id]
,block.[work_block_name]
,section.[work_section_id]
,section.[work_section_name]
,activity.[work_activity_id]
,activity.[work_activity_name]
from [KGWS].[dbo].[work_activity] activity
inner join [KGWS].[dbo].[work_section] section
on activity.[work_section_id]=section.[work_section_id]
inner join [KGWS].[dbo].[work_block] block
on section.[work_block_id]=block.[work_block_id]
";
parameters.Clear();
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
list.Add(new WorkEntity(
Convert.ToInt32(reader["work_block_id"]),
Convert.ToString(reader["work_block_name"]),
Convert.ToInt32(reader["work_section_id"]),
Convert.ToString(reader["work_section_name"]),
Convert.ToInt32(reader["work_activity_id"]),
Convert.ToString(reader["work_activity_name"])));
});
return(list);
}
public List <PaletteEntity> GetPalettes()
{
var parameters = new List <SqlParameter>();
var list = new List <PaletteEntity>();
var sql = @"
select
[palette_id]
,[user_id]
,[palette_name]
,[use_seg]
,[use_place]
,[is_deleted]
from [KGWS].[dbo].[palettes]
where
[is_deleted]=@is_deleted
order by [palette_name]
";
parameters.Clear();
parameters.Add(new SqlParameter("@is_deleted", false));
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
list.Add(new PaletteEntity(
Convert.ToInt32(reader["palette_id"]),
Convert.ToInt32(reader["user_id"]),
Convert.ToString(reader["palette_name"]),
Convert.ToString(reader["use_seg"]),
Convert.ToString(reader["use_place"]),
Convert.ToBoolean(reader["is_deleted"])));
});
return(list);
}
public void ActiityChangeSave(PerformanceEntity performance)
{
var parameters = new List <SqlParameter>();
var sql = @"
insert into
[KGWS].[dbo].[work_action]
(
[block_name]
,[section_name]
,[activity_name]
,[user_id]
)
values
(
@block_name
,@section_name
,@activity_name
,@user_id
)
select SCOPE_IDENTITY() as id
";
parameters.Clear();
parameters.Add(new SqlParameter("@block_name", performance.WorkEntity.WorkBlockName));
parameters.Add(new SqlParameter("@section_name", performance.WorkEntity.WorkSectionName));
parameters.Add(new SqlParameter("@activity_name", performance.WorkEntity.WorkActivityName));
parameters.Add(new SqlParameter("@user_id", performance.User.UserId));
var id = 0;
SQLServerHelper.Query(
sql,
parameters.ToArray(),
reader =>
{
id = Convert.ToInt32(reader["id"]);
});
sql = @"
insert into
[KGWS].[dbo].[Time]
(
[start_time]
,[end_time]
,[action_id]
,[milliseconds]
,[seconds]
,[minutes]
,[hours]
,[days]
,[total_milliseconds]
,[total_seconds]
,[total_minutes]
,[total_hours]
,[total_days]
)
values
(
@start_time
,@end_time
,@action_id
,@milliseconds
,@seconds
,@minutes
,@hours
,@days
,@total_milliseconds
,@total_seconds
,@total_minutes
,@total_hours
,@total_days
)
";
parameters.Clear();
parameters.Add(new SqlParameter("@start_time", performance.StartTime));
parameters.Add(new SqlParameter("@end_time", performance.EndTime));
parameters.Add(new SqlParameter("@action_id", id));
parameters.Add(new SqlParameter("@milliseconds", performance.TimeSpanMs));
parameters.Add(new SqlParameter("@seconds", performance.TimeSpanS));
parameters.Add(new SqlParameter("@minutes", performance.TimeSpanM));
parameters.Add(new SqlParameter("@hours", performance.TimeSpanH));
parameters.Add(new SqlParameter("@days", performance.TimeSpanD));
parameters.Add(new SqlParameter("@total_milliseconds", performance.TotalTimeMs));
parameters.Add(new SqlParameter("@total_seconds", performance.TotalTimeS));
parameters.Add(new SqlParameter("@total_minutes", performance.TotalTimeM));
parameters.Add(new SqlParameter("@total_hours", performance.TotalTimeH));
parameters.Add(new SqlParameter("@total_days", performance.TotalTimeD));
SQLServerHelper.Execute(sql, parameters.ToArray());
}