public class HourlyProductionBL
{
SqlConnection sqlCon = new SqlConnection(DBConnection.ConnectionString);
SqlCommand sqlCom = null;
HourlyProduction _oHP = new HourlyProduction();
List<HourlyProduction> _oHPs = new List<HourlyProduction>();
HourlyProductionDetail _oHPD = new HourlyProductionDetail();
List<HourlyProductionDetail> _oHPDs = new List<HourlyProductionDetail>();
HourlyProductionDetailBL _oHPDBL = new HourlyProductionDetailBL();
public HourlyProduction Save(HourlyProduction hourlyProduction, long userId)
{
_oHP = new HourlyProduction();
SqlTransaction transaction = null;
using (SqlConnection sqlCon = new SqlConnection(DBConnection.ConnectionString))
{
try
{
sqlCon.Open();
transaction = sqlCon.BeginTransaction();
var oHPDs = new List<HourlyProductionDetail>();
foreach (var oHP in hourlyProduction.HourlyProductions)
{
oHP.UserId = hourlyProduction.UserId;
oHP.UnitId = hourlyProduction.UnitId;
if (!oHP.DeleteIt && oHP.StyleId > 0)
{
oHPDs = new List<HourlyProductionDetail>();
oHPDs = oHP.HourlyProductionDetails;
sqlCom = new SqlCommand("SP_HourlyProduction", sqlCon, transaction);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, oHP, oHP.Id == 0 ? EnumOperationTypes.Save : EnumOperationTypes.Update, userId);
SqlDataReader reader = sqlCom.ExecuteReader();
var oHourlyProduction = new HourlyProduction();
while (reader.Read()) oHourlyProduction = this.Mapping(reader);
reader.Close();
if (oHourlyProduction.Id > 0)
{
List<long> listHPDId = new List<long>();
var oHPDBL = new HourlyProductionDetailBL();
foreach (var oHPD in oHPDs)
{
if (!oHPD.DeleteIt)
{
oHPD.HourlyProductionId = oHourlyProduction.Id;
sqlCom = new SqlCommand("SP_HourlyProductionDetail", sqlCon, transaction);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = oHPDBL.SetParameters(sqlCom, oHPD, oHPD.Id == 0 ? EnumOperationTypes.Save : EnumOperationTypes.Update, userId);
reader = sqlCom.ExecuteReader();
var hourlyProductionDetail = new HourlyProductionDetail();
while (reader.Read()) hourlyProductionDetail = oHPDBL.Mapping(reader);
reader.Close();
listHPDId.Add(hourlyProductionDetail.Id);
if (hourlyProductionDetail.Id > 0) oHourlyProduction.HourlyProductionDetails.Add(hourlyProductionDetail);
}
else
{
if (oHPD.Id > 0)
{
sqlCom = new SqlCommand("SP_HourlyProductionDetail", sqlCon, transaction);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = oHPDBL.SetParameters(sqlCom, oHPD, EnumOperationTypes.Delete, userId);
sqlCom.ExecuteNonQuery();
}
}
}
#region Delete remaining item
_oHP.HourlyProductions.Add(oHourlyProduction);
if (listHPDId.Count > 0)
{
string sql = "DELETE FROM HourlyProductionDetail WHERE HourlyProductionId = " + oHourlyProduction.Id +
" AND Id NOT IN (" + string.Join(",", listHPDId.Select(x => x)) + ")";
sqlCom = new SqlCommand(sql, sqlCon, transaction);
sqlCom.ExecuteNonQuery();
}
#endregion
}
}
else if (oHP.Id > 0 && oHP.DeleteIt)
{
sqlCom = new SqlCommand("SP_HourlyProduction", sqlCon, transaction);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, oHP, EnumOperationTypes.Delete, userId);
sqlCom.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
_oHP = new HourlyProduction() { Message = ex.Message.Split('~')[0] };
transaction.Rollback();
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
}
return _oHP;
}
public HourlyProduction MakeDayComplete(HourlyProduction hourlyProduction, long userId)
{
try
{
sqlCon.Open();
sqlCom = new SqlCommand("SP_HourlyProduction", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, hourlyProduction, EnumOperationTypes.CompleteDay, userId);
sqlCon.Close();
sqlCon.Open();
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) _oHP = this.Mapping(reader);
//if (_oHP.Id > 0 && _oHP.IsComplete) _oHP.ProductionDate = _oHP.ProductionDate.AddDays(1);
}
catch (Exception ex)
{
_oHP = new HourlyProduction() { Message = ex.Message.Split('~')[0] };
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
return _oHP;
}
public HourlyProduction MakeDaySkip(HourlyProduction hourlyProduction, long userId)
{
try
{
sqlCon.Open();
sqlCom = new SqlCommand("SP_HourlyProduction", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, hourlyProduction, EnumOperationTypes.SkipDay, userId);
sqlCon.Close();
sqlCon.Open();
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) _oHP = this.Mapping(reader);
//if (_oHP.Id > 0 && _oHP.IsComplete) _oHP.ProductionDate = _oHP.ProductionDate.AddDays(1);
}
catch (Exception ex)
{
_oHP = new HourlyProduction() { Message = ex.Message.Split('~')[0] };
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
return _oHP;
}
public List<HourlyProduction> GetsHourlyProductionWithDetails(string buyerIds, string styleIds, int userId)
{
try
{
sqlCon.Open();
string sql = "SELECT * FROM View_HourlyProduction WHERE Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId=" + userId + ") ";
if (!string.IsNullOrEmpty(buyerIds)) sql = sql + " AND Buyer_id IN ('" + buyerIds + "')";
if (!string.IsNullOrEmpty(styleIds)) sql = sql + " AND Style_id IN ('" + styleIds + "')";
sqlCom = new SqlCommand(sql, sqlCon);
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) _oHPs.Add(this.Mapping(reader));
sqlCon.Close();
if (_oHPs.Count > 0)
{
string hpIds = string.Join(",", _oHPs.Select(x => x.Id));
sqlCon.Open();
sql = "SELECT * FROM HourlyProductionDetail WHERE HourlyProductionId IN (" + hpIds + ")";
sqlCom = new SqlCommand(sql, sqlCon);
reader = sqlCom.ExecuteReader();
while (reader.Read()) _oHPDs.Add(_oHPDBL.Mapping(reader));
sqlCon.Close();
if (_oHPDs.Count > 0)
{
foreach (HourlyProduction hp in _oHPs) hp.HourlyProductionDetails = _oHPDs.Where(x => x.HourlyProductionId == hp.Id).ToList();
}
}
}
catch (Exception ex)
{
_oHPs = new List<HourlyProduction>();
_oHPs.Add(new HourlyProduction() { Message = ex.Message.Split('~')[0] });
}
finally
{
sqlCom.Dispose();
}
return _oHPs;
}
private SqlCommand SetParameters(SqlCommand sqlCom, HourlyProduction hourlyProduction, EnumOperationTypes operationType, long userId)
{
if (string.IsNullOrEmpty(hourlyProduction.ItemName)) hourlyProduction.ItemName = "";
if (string.IsNullOrEmpty(hourlyProduction.Remarks)) hourlyProduction.Remarks = "";
sqlCom.Parameters.AddWithValue("@Id", hourlyProduction.Id);
sqlCom.Parameters.AddWithValue("@ProductionDate", hourlyProduction.ProductionDate);
sqlCom.Parameters.AddWithValue("@UnitId", hourlyProduction.UnitId);
sqlCom.Parameters.AddWithValue("@LineId", hourlyProduction.LineId);
sqlCom.Parameters.AddWithValue("@Buyer_id", hourlyProduction.Buyer_id);
sqlCom.Parameters.AddWithValue("@ItemName", hourlyProduction.ItemName.ToUpper());
sqlCom.Parameters.AddWithValue("@IsLearningCurve", hourlyProduction.IsLearningCurve);
sqlCom.Parameters.AddWithValue("@InputDate", hourlyProduction.InputDate);
sqlCom.Parameters.AddWithValue("@Remarks", hourlyProduction.Remarks);
sqlCom.Parameters.AddWithValue("@StyleId", hourlyProduction.StyleId);
sqlCom.Parameters.AddWithValue("@UserId", userId);
sqlCom.Parameters.AddWithValue("@InsertDate", hourlyProduction.InsertDate);
sqlCom.Parameters.AddWithValue("@OperationType", operationType);
return sqlCom;
}
private HourlyProduction Mapping(SqlDataReader reader, MapperType mapperType = MapperType.ViewProps)
{
var oHP = new HourlyProduction();
oHP.Id = (long)reader["Id"];
oHP.ProductionDate = (DateTime)reader["ProductionDate"];
oHP.UnitId = (int)reader["UnitId"];
oHP.LineId = (int)reader["LineId"];
oHP.Manpower = string.IsNullOrEmpty(reader["Manpower"].ToString()) ? 0 : (int)reader["Manpower"];
oHP.Buyer_id = (long)reader["Buyer_id"];
oHP.ItemName = reader["ItemName"].ToString();
oHP.IsLearningCurve = (int)reader["IsLearningCurve"];
oHP.InputDate = (DateTime)reader["InputDate"];
oHP.Remarks = reader["Remarks"].ToString();
oHP.StyleId = (int)reader["StyleId"];
oHP.UserId = (long)reader["UserId"];
if (mapperType == MapperType.ViewProps)
{
oHP.InsertDate = (DateTime)reader["InsertDate"];
oHP.BuyerName = reader["Buyer_name"].ToString();
oHP.LineName = reader["LineName"].ToString();
oHP.StyleNo = reader["StyleNo"].ToString();
}
else if (mapperType == MapperType.OnlyObjectProps)
{
oHP.InsertDate = (DateTime)reader["InsertDate"];
}
else if (mapperType == MapperType.ForInputDate)
{
oHP.StyleNo = reader["StyleNo"].ToString();
}
return oHP;
}
public HourlyProduction GetInputDate(HourlyProduction oHP, int unitId, int userId)
{
_oHP = new HourlyProduction();
try
{
string sql = string.Format(@"DECLARE @MinDate DATE = '01-Mar-2019',
@MaxDate DATE = '{0}' SELECT * FROM (
SELECT TOP(1)* FROM View_HourlyProductionForInputDate
WHERE StyleId={1}
AND UnitId={2}
AND LineId={3}
AND Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId={4})
AND CAST(ProductionDate AS DATE) BETWEEN @MinDate AND @MaxDate
AND CAST(ProductionDate AS DATE) NOT IN (SELECT CAST(HolidayDate AS DATE) FROM Holiday)
AND CAST(ProductionDate AS DATE) < '{0}'
ORDER BY ProductionDate
) HP1 ",
oHP.ProductionDate.ToString("dd-MMM-yyyy"),
oHP.StyleId,
unitId,
oHP.LineId,
userId);
var oHPs = Data<HourlyProduction>.DataSource(sql);
if (oHPs != null && oHPs.Count > 0) _oHP = oHPs[0];
else _oHP = new HourlyProduction();
}
catch (Exception ex)
{
_oHP = new HourlyProduction() { Message = ex.Message.Split('~')[0] };
}
return _oHP;
}
public List<HourlyProduction> GetInputDates(List<HourlyProduction> oHourlyProductions, int unitId, int userId)
{
_oHPs = new List<HourlyProduction>();
try
{
if (oHourlyProductions.Count() > 0)
{
string sql = string.Format(@"DECLARE @MinDate DATE = '01-Mar-2019',
@MaxDate DATE = '{0}'",
oHourlyProductions.FirstOrDefault().ProductionDate.ToString("dd-MMM-yyyy"));
int count = 0;
foreach (var oHP in oHourlyProductions)
{
count++;
sql += string.Format(@" SELECT * FROM (
SELECT TOP(1)* FROM View_HourlyProductionForInputDate
WHERE StyleId={0}
AND UnitId={1}
AND LineId={2}
AND Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId={3})
AND CAST(ProductionDate AS DATE) BETWEEN @MinDate AND @MaxDate
AND CAST(ProductionDate AS DATE) NOT IN (SELECT CAST(HolidayDate AS DATE) FROM Holiday)
AND CAST(ProductionDate AS DATE) < '{4}'
ORDER BY ProductionDate
) HP{5} ",
oHP.StyleId,
unitId,
oHP.LineId,
userId,
oHP.ProductionDate.ToString("dd-MMM-yyyy"),
count);
if (count < oHourlyProductions.Count()) sql += " UNION ALL ";
}
using (SqlConnection sqlCon = new SqlConnection(DBConnection.ConnectionString))
{
if (sqlCon.State == ConnectionState.Closed) sqlCon.Open();
var sqlCom = new SqlCommand(sql, sqlCon);
var reader = sqlCom.ExecuteReader();
while (reader.Read()) _oHPs.Add(this.Mapping(reader, MapperType.ForInputDate));
reader.Close();
}
}
}
catch (Exception ex)
{
_oHPs = new List<HourlyProduction>();
_oHP = new HourlyProduction() { Message = ex.Message.Split('~')[0] };
_oHPs.Add(_oHP);
}
return _oHPs;
}
public List<HourlyProduction> GetProductionByDateUnit(string selectedDate, int lineId, int unitId, int userId)
{
string sql = string.Format(@"SELECT * FROM View_HourlyProduction WHERE ProductionDate = '{0}' AND UnitId={1} AND PreviousId={2}", selectedDate, unitId, lineId);
var oHPs = Data<HourlyProduction>.DataSource(sql);
return oHPs;
}
public List<HourlyProduction> GetProductionWithDetails(string selectedDate, int unitId)
{
_oHPs = new List<HourlyProduction>();
if (unitId > 0)
{
string sql = string.Format(@"SELECT * FROM View_HourlyProduction WHERE ProductionDate = '{0}' AND UnitId={1}", selectedDate, unitId);
var oHPs = Data<HourlyProduction>.DataSource(sql);
if (oHPs != null && oHPs.Count() > 0)
{
_oHPs = oHPs;
sql = string.Format(@"SELECT * FROM HourlyProductionDetail WHERE HourlyProductionId IN ({0})", string.Join(",", _oHPs.Select(x => x.Id)));
var oHPDs = Data<HourlyProductionDetail>.DataSource(sql);
if (oHPs != null && oHPs.Count() > 0) _oHPs.ForEach(x => x.HourlyProductionDetails = oHPDs.Where(y => y.HourlyProductionId == x.Id).ToList());
}
}
return _oHPs;
}
#region Without mapping
public List<HourlyProduction> GetsNew(DateTime FromDate, DateTime ToDate, long UnitId, int userId, bool bIsHameemErpUser = false)
{
string sSQL = "SELECT * FROM View_HourlyProduction HP WHERE HP.Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId=" + userId + ") AND HP.UnitId=" + UnitId + " AND CONVERT(Date, HP.ProductionDate) BETWEEN '" + FromDate.ToString("yyyy-MM-dd") + "' AND '" + ToDate.ToString("yyyy-MM-dd") + "'";
if (bIsHameemErpUser) sSQL = "SELECT * FROM View_HourlyProduction HP WHERE HP.Buyer_id IN (SELECT B.Buyer_id FROM View_BuyerForHameemUser B WHERE B.UserId=" + userId + ") AND HP.UnitId=" + UnitId + " AND CONVERT(Date, HP.ProductionDate) BETWEEN '" + FromDate.ToString("yyyy-MM-dd") + "' AND '" + ToDate.ToString("yyyy-MM-dd") + "'";
return Data<HourlyProduction>.DataSource(sSQL);
}
public List<HourlyProduction> GetsDataFromPreviousEntryDate(long UnitId, int userId)
{
string sSQL = "SELECT * FROM View_HourlyProduction HP WHERE HP.Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId=" + userId + ") AND HP.UnitId=" + UnitId + " AND CONVERT(Date, HP.ProductionDate) = (SELECT MAX(HP1.ProductionDate) FROM HourlyProduction HP1 WHERE HP1.UnitId=" + UnitId + ")";
return Data<HourlyProduction>.DataSource(sSQL);
}
public List<HourlyProduction> GetRunningDateDatas(int unitId, int userId)
{
string sSQL = string.Format(@"SELECT * FROM View_HourlyProduction HP
LEFT JOIN HourlyProductionDay HPD ON HPD.UnitId=HP.UnitId AND FORMAT(HPD.HPDate,'dd-MMM-yyyy') = FORMAT(HP.ProductionDate,'dd-MMM-yyyy')
WHERE HPD.IsComplete=0
AND HP.UnitId={0}
AND HP.Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId={1})
ORDER BY HP.Id, HP.LineId, HP.LineName", unitId, userId);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
public HourlyProduction GetItemName(int styleId)
{
string sSQL = string.Format(@"SELECT TOP(1)* FROM HourlyProduction WHERE StyleId = {0} ORDER BY Id DESC", styleId);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new HourlyProduction();
return oHPs[0];
}
public int GetMissingCostRecord(long unitId, int userId)
{
string sSQL = string.Format(@"SELECT COUNT(DISTINCT(HP.StyleId)) AS MissingCost FROM View_HourlyProduction HP
LEFT JOIN CP_Style S ON S.Id=HP.StyleId
WHERE ISNULL(S.CMFactoryTK,0) = 0
AND HP.UnitId={0}
AND HP.Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId={1})", unitId, userId);
var oHPs = Data<HourlyProductionOtherProperties>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return 0;
return oHPs[0].MissingCost;
}
public List<HourlyProduction> GetMissingCostStyleList(long unitId, int userId)
{
string sSQL = string.Format(@"SELECT DISTINCT(HP.StyleId),S.StyleNo,HP.Buyer_name FROM View_HourlyProduction HP
LEFT JOIN CP_Style S ON S.Id=HP.StyleId
WHERE ISNULL(S.CMFactoryTK,0) = 0
AND HP.UnitId={0}
AND HP.Buyer_id IN (SELECT B.Buyer_id FROM View_Buyer B WHERE B.UserId={1})
ORDER BY HP.Buyer_name, S.StyleNo", unitId, userId);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
public List<HourlyProduction> GetsHourlyProductionForBuyer(DateTime productionDate, int buyerId, int userId, bool bIsHameemErpUser = false)
{
string sSQL = string.Format(@"SELECT * FROM View_HourlyProduction HP
LEFT JOIN View_Buyer B ON B.Buyer_id=HP.Buyer_id AND B.UserId = {2}
WHERE HP.UnitId IN (SELECT UUP.UnitId FROM UserUnitPer UUP WHERE UUP.UserId = {2})
AND HP.Buyer_id={0} AND FORMAT(HP.ProductionDate,'dd-MMM-yyyy') = '{1}'
ORDER BY UnitName", buyerId, productionDate.ToString("dd-MMM-yyyy"), userId);
if (bIsHameemErpUser)
{
sSQL = string.Format(@"SELECT * FROM View_HourlyProduction HP
LEFT JOIN View_BuyerForHameemUser B ON B.Buyer_id=HP.Buyer_id AND B.UserId = {2}
WHERE HP.Buyer_id={0} AND FORMAT(HP.ProductionDate,'dd-MMM-yyyy') = '{1}'
ORDER BY UnitName", buyerId, productionDate.ToString("dd-MMM-yyyy"), userId);
}
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
public List<HourlyProduction> GetProductionBasicInfo(int lineId, string prodDate)
{
string sSQL = string.Format(@"SELECT * FROM HourlyProduction
WHERE ProductionDate='{0}' AND LineId={1}", prodDate, lineId);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
#endregion
#region Buyer Related
public List<HourlyProduction> GetDateBetweenProdBuyers(string fromDate, string toDate, User user)
{
string unitCon = "";
if (user.USERTYPE == 3.ToString() || user.USRTYPE == 3.ToString()) //DataEntry
{
unitCon = " AND HP.UnitId = " + user.BRANCH_CODE + " ";
}
string sSQL = string.Format(@"SELECT distinct B.Buyer_id, B.Buyer_name AS BuyerName
FROM HourlyProduction HP
LEFT JOIN hameem_erp.dbo.Reg_Buyer B ON B.Buyer_id=HP.Buyer_id
WHERE HP.ProductionDate BETWEEN '{0}' AND '{1}'
AND HP.Buyer_id IN (SELECT UBP.Buyer_Id FROM UserBuyerPer UBP WHERE UBP.UserId = {2})
AND HP.UnitId IN (SELECT UUP.UnitId FROM UserUnitPer UUP WHERE UUP.UserId = {2})
{3}
ORDER BY B.Buyer_name", fromDate, toDate, user.USERID, unitCon);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
#endregion
#region Style Related
public List<HourlyProduction> GetDateBetweenProdStyles(string fromDate, string toDate, int buyerId, User user)
{
if (buyerId > 0)
{
string unitCon = "";
if (user.USERTYPE == 3.ToString() || user.USRTYPE == 3.ToString()) //DataEntry
{
unitCon = " AND HP.UnitId = " + user.BRANCH_CODE + " ";
}
string sSQL = string.Format(@"SELECT distinct S.Id AS StyleId, S.StyleNo
FROM HourlyProduction HP
LEFT JOIN CP_Style S ON S.Id=HP.StyleId
WHERE HP.ProductionDate BETWEEN '{0}' AND '{1}'
AND HP.Buyer_id IN (SELECT UBP.Buyer_Id FROM UserBuyerPer UBP WHERE UBP.UserId = {2})
AND HP.UnitId IN (SELECT UUP.UnitId FROM UserUnitPer UUP WHERE UUP.UserId = {2})
AND HP.Buyer_id={3}
{4}
ORDER BY S.StyleNo", fromDate, toDate, user.USERID, buyerId, unitCon);
var oHPs = Data<HourlyProduction>.DataSource(sSQL);
if (oHPs == null || oHPs.Count == 0) return new List<HourlyProduction>();
return oHPs;
}
return new List<HourlyProduction>();
}
#endregion
}
=======================================================
USE [ProductionManagementLive]
GO
/****** Object: StoredProcedure [dbo].[SP_HourlyProduction] Script Date: 29-Dec-21 12:03:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Imrez Khan Ratin
-- Create date: 30 May 2018
-- =============================================
ALTER PROCEDURE [dbo].[SP_HourlyProduction]
@Id bigint
,@ProductionDate date
,@UnitId int
,@LineId int
,@Buyer_id bigint
,@StyleId int
,@ItemName nvarchar(100)
,@IsLearningCurve int
,@InputDate date
,@Remarks nvarchar(MAX)
,@InsertDate datetime
,@UserId bigint
,@OperationType int
AS
BEGIN TRAN
DECLARE
@StyleNo nvarchar(100)
,@BuyerName nvarchar(100)
,@Message nvarchar(MAX)
IF(@UserId > 0)
BEGIN
IF(ISNULL((SELECT branch_code FROM UserInfo WHERE UserId = @UserId),0) != @UnitId)
BEGIN
ROLLBACK
RAISERROR (N'Invalid user.. !!!~',16,1);
RETURN
END
END
IF(@OperationType != 3)
BEGIN
IF EXISTS(SELECT * FROM WorkOnOffDay WHERE HourlyProduction=1 AND UnitId = @UnitId AND FORMAT(WorkingDay,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
SET @Message = 'a'
END
ELSE IF EXISTS(SELECT * FROM Holiday WHERE FORMAT(HolidayDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This date is not a valid working day. !!!~',16,1);
RETURN
END
END
IF(@OperationType = 1)
BEGIN
IF(@LineId <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Line !!!~',16,1);
RETURN
END
IF(@Buyer_id <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Buyer !!!~',16,1);
RETURN
END
IF(@StyleId <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Style !!!~',16,1);
RETURN
END
IF (@UnitId = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid unit Id !!!~',16,1);
RETURN
END
IF (@UserId = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid user !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=0 AND FORMAT(HPDate,'dd-MMM-yyyy') != FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
DECLARE @RunningDate varchar(20)
SET @RunningDate = (SELECT FORMAT(HPDate,'dd-MMM-yyyy') FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=0)
RAISERROR (N'You have already a running date (%s) , Complate running date first . !!!~',16,1, @RunningDate);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=1 AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This day already completed. !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=2 AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This day already skipped. !!!~',16,1);
RETURN
END
IF(@Buyer_id != (SELECT Buyer_id FROM CP_Style WHERE Id=@StyleId))
BEGIN
ROLLBACK
SET @StyleNo = (SELECT StyleNo FROM CP_Style WHERE Id=@StyleId)
SET @BuyerName = (SELECT Buyer_name FROM View_Buyer WHERE Buyer_id=@Buyer_id AND UserId=@UserId)
SET @Message = N'Style No ' + @StyleNo + ' is not of buyer ' +@BuyerName+ '. !!!~'
RAISERROR (@Message,16,1);
RETURN
END
IF(@StyleId > 0 AND RTRIM(LTRIM(@ItemName)) != '' AND (SELECT ISNULL(Item,'') FROM CP_Style WHERE Id=@StyleId) = '')
BEGIN
UPDATE CP_Style SET Item=UPPER(@ItemName) WHERE Id=@StyleId
END
IF NOT EXISTS(SELECT * FROM HourlyProduction WHERE UnitId=@UnitId AND FORMAT(ProductionDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy') AND LineId=@LineId AND Buyer_id=@Buyer_id AND StyleId=@StyleId)
BEGIN
IF NOT EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
SET @Id = (SELECT ISNULL(MAX(Id),0) FROM HourlyProductionDay) + 1
INSERT INTO HourlyProductionDay(Id, HPDate, IsComplete, UnitId, UserId, EntryDate)
VALUES(@Id, @ProductionDate, 0, @UnitId, @UserId, GETDATE())
END
SET @Id = (SELECT ISNULL(MAX(Id),0) FROM HourlyProduction) + 1
INSERT INTO HourlyProduction (Id, UnitId, ProductionDate, LineId, Buyer_id, StyleId, ItemName, IsLearningCurve, InputDate, Remarks, InsertDate, UserId)
VALUES(@Id, @UnitId, @ProductionDate, @LineId, @Buyer_id, @StyleId, @ItemName, @IsLearningCurve, @InputDate, @Remarks, GETDATE(), @UserId)
SELECT * FROM View_HourlyProduction WHERE Id = @Id
END
ELSE
BEGIN
SELECT * FROM View_HourlyProduction WHERE UnitId=@UnitId AND FORMAT(ProductionDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy') AND LineId=@LineId AND Buyer_id=@Buyer_id AND StyleId=@StyleId
END
END
ELSE IF(@OperationType = 2) --UPDATE
BEGIN
IF(@LineId <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Line !!!~',16,1);
RETURN
END
IF(@Buyer_id <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Buyer !!!~',16,1);
RETURN
END
IF(@StyleId <= 0)
BEGIN
ROLLBACK
RAISERROR (N'Select Style !!!~',16,1);
RETURN
END
IF (@UserId = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid user !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=1 AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This day already completed. !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=2 AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This day already skipped. !!!~',16,1);
RETURN
END
IF(@Buyer_id != (SELECT Buyer_id FROM CP_Style WHERE Id=@StyleId))
BEGIN
ROLLBACK
SET @StyleNo = (SELECT StyleNo FROM CP_Style WHERE Id=@StyleId)
SET @BuyerName = (SELECT Buyer_name FROM View_Buyer WHERE Buyer_id=@Buyer_id AND UserId=@UserId)
SET @Message = N'Style No ' + @StyleNo + ' is not of buyer ' +@BuyerName+ '. !!!~'
RAISERROR (@Message,16,1);
RETURN
END
IF(@StyleId > 0 AND RTRIM(LTRIM(@ItemName)) != '' AND (SELECT ISNULL(Item,'') FROM CP_Style WHERE Id=@StyleId) = '')
BEGIN
UPDATE CP_Style SET Item=UPPER(@ItemName) WHERE Id=@StyleId
END
UPDATE HourlyProduction SET ProductionDate=@ProductionDate,
LineId=@LineId,
Buyer_id=@Buyer_id,
StyleId=@StyleId,
ItemName=@ItemName,
IsLearningCurve=@IsLearningCurve,
InputDate=@InputDate,
Remarks=@Remarks,
UserId=@UserId
WHERE Id=@Id
SELECT * FROM View_HourlyProduction WHERE Id = @Id
END
ELSE IF(@OperationType = 3) --Delete
BEGIN
IF (@Id = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid hourly production !!!~',16,1);
RETURN
END
DELETE FROM HourlyProductionDetail WHERE HourlyProductionId = @Id
DELETE FROM HourlyProduction WHERE Id = @Id
END
ELSE IF(@OperationType = 17) --Date Complete
BEGIN
IF (@UnitId = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid unit Id !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND IsComplete=1 AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'This day already completed. !!!~',16,1);
RETURN
END
IF NOT EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
ROLLBACK
RAISERROR (N'Invalid day complete (No saved data found) !!!~',16,1);
RETURN
END
IF NOT EXISTS(SELECT * FROM HourlyProductionDetail WHERE HourlyProductionId IN (SELECT HourlyProduction.Id FROM HourlyProduction WHERE UnitId=@UnitId AND FORMAT(ProductionDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy')))
BEGIN
ROLLBACK
RAISERROR (N'Without any quantity day cannot complete. !!!~',16,1);
RETURN
END
UPDATE HourlyProductionDay SET IsComplete=1, UserId=@UserId WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy')
UPDATE HourlyProdDateOpen SET IsComplete=1 WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy')
IF EXISTS(SELECT * FROM HourlyProdDateOpen WHERE UnitId=@UnitId AND IsComplete=0)
BEGIN
DECLARE @TempDate datetime
SET @TempDate = (SELECT TOP(1)HPDate FROM HourlyProdDateOpen WHERE UnitId=@UnitId AND IsComplete=0 ORDER BY HPDate)
IF NOT EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND HPDate=@TempDate)
BEGIN
DECLARE
@HourlyProductionDayId int = (SELECT ISNULL(MAX(Id),0) FROM HourlyProductionDay) + 1
INSERT INTO HourlyProductionDay (Id, HPDate, IsComplete,UnitId, UserId, EntryDate, IsCompletedBySystem)
VALUES (@HourlyProductionDayId,@TempDate,0, @UnitId, @UserId, GETDATE(), 0)
END
ELSE
BEGIN
UPDATE HourlyProductionDay SET IsComplete=0 WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@TempDate,'dd-MMM-yyyy')
END
END
SELECT TOP(1)* FROM View_HourlyProduction WHERE CONVERT(DATE,ProductionDate) = CONVERT(DATE,@ProductionDate) AND UnitId=@UnitId
END
ELSE IF(@OperationType = 18) --Date skip
BEGIN
IF (@UnitId = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid unit Id !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM HourlyProductionDetail WHERE HourlyProductionId IN (SELECT HourlyProduction.Id FROM HourlyProduction WHERE UnitId=@UnitId AND FORMAT(ProductionDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy')))
BEGIN
ROLLBACK
RAISERROR (N'Already have data, cannot skip. !!!~',16,1);
RETURN
END
IF NOT EXISTS(SELECT * FROM HourlyProductionDay WHERE UnitId=@UnitId AND FORMAT(HPDate,'dd-MMM-yyyy') = FORMAT(@ProductionDate,'dd-MMM-yyyy'))
BEGIN
SET @Id = (SELECT ISNULL(MAX(Id),0) FROM HourlyProductionDay) + 1
INSERT INTO HourlyProductionDay(Id, HPDate, IsComplete, UnitId, UserId, EntryDate)
VALUES(@Id, @ProductionDate, 2, @UnitId, @UserId, GETDATE())
END
ELSE
BEGIN
ROLLBACK
RAISERROR (N'Already have data on this date, unable to skip !!!~',16,1);
RETURN
END
SELECT TOP(1)* FROM View_HourlyProduction WHERE CONVERT(DATE,ProductionDate) = CONVERT(DATE,@ProductionDate) AND UnitId=@UnitId
END
COMMIT TRAN
0 Comments