public List <MessageModel> SaveScrapRecvdData(ScrapModel scrap)
{
DataTable dtDetails;
dtDetails = ScrapProductDetails(scrap.ScrapProductDetails);
List <MessageModel> response = new List <MessageModel>();
try
{
response = _db.Query <MessageModel>("CRUD_SCRAP_RECVED_REQUEST",
new
{
P_SCRAPID = scrap.SCRAPID,
P_SCRAPNO = scrap.SCRAPNO,
TempTableDetails = dtDetails.AsTableValuedParameter("Type_SCRAP_PRODUCT_DETAILS")
},
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dtDetails = null;
}
return(response);
}
public static void UpdateScrap(ScrapModel scrapModel)
{
string procedure = "[dbo].[UpdateScrap]";
var p = new DynamicParameters();
p.Add("@DatePosted", scrapModel.DatePosted);
p.Add("@ScrapinOrderUnit", scrapModel.ScrapinOrderUnit);
p.Add("@OrderUnit", scrapModel.OrderUnit);
p.Add(" @stdcost", scrapModel.StdCost);
p.Add(" @valuatedstock", scrapModel.Valuatedstock);
p.Add("@OperScrapCost", scrapModel.OperScrapCost);
p.Add("@Totoperscrapcost", scrapModel.Totoperscrapcost);
p.Add("@YieldtoBeConfirmed", scrapModel.YieldtoBeConfirmed);
p.Add(" @ScraptoBeConfirmed", scrapModel.ScraptoBeConfirmed);
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
}
catch (Exception e)
{
txtWriter.writeInfo($"{scrapModel.ConfirmationNumber}-{scrapModel.ConfirmationNumber}, {e}");
throw;
}
}
}
internal bool ReadList(string args, out List <ScrapModel> data)
{
args = args.Equals("全部") ? "" : "where Name='" + args + "'";
bool flag = true;
data = new List <ScrapModel>();
string sql = "select GUID,Number,Name,Remark,Operator,strftime(Date) as Date from T_Warehouse_Scrap " + args + " order by Date";
DataSet ds = new DataSet();
flag = new Helper.SQLite.DBHelper().QueryData(sql, out ds);
if (flag)
{
int id = 1;
foreach (DataRow dr in ds.Tables[0].Rows)
{
ScrapModel d = new ScrapModel();
d.Guid = (Guid)dr["GUID"];
d.Id = id++;
d.Date = dr["Date"].ToString().Split(' ')[0];
d.Number = dr["Number"].ToString();
d.Name = dr["Name"].ToString();
d.Remark = dr["Remark"].ToString();
d.Operator = dr["Operator"].ToString();
data.Add(d);
}
}
return(flag);
}
public static int SelectScrapID(ScrapModel scrapModel)
{
string procedure = "[dbo].[SelectScrapID]";
var p = new DynamicParameters();
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
p.Add("@ConfirmationNumber", scrapModel.ConfirmationNumber);
p.Add("@ConfirmationCounter", scrapModel.ConfirmationCounter);
int i;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
i = p.Get <int>("@id");
}
catch (Exception e)
{
txtWriter.writeInfo($"{scrapModel.ConfirmationNumber}, {e}");
throw;
}
}
return(i);
}
public ActionResult SaveScrapProduct(ScrapModel model)
{
try
{
var user = (ViewUser)Session["user"];
model.UserId = user.UserId;
model.ScannedProducts = GetTempScrapProducts(GetTempScrapXmlFilePath()).ToList();
var result = _iScrapManager.SaveScrap(model);
return(View());
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw;
}
}
private int SaveScrapItemWithQuantity(ScrapModel model, long masterId)
{
int i = 0;
foreach (var item in model.ScannedProducts.GroupBy(n => n.ProductId))
{
CommandObj.CommandText = "UDSP_SaveScrapItemWithQuantity";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@ProductId", item.Key);
CommandObj.Parameters.AddWithValue("@Quantity", item.Count());
CommandObj.Parameters.AddWithValue("@ScrapId", masterId);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
i += Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
}
return(i);
}
public static void InsertScrap(ScrapModel scrapModel)
{
string procedure = "[dbo].[InsertScrap]";
object values = new
{
@WorkCenter = scrapModel.WorkCenter,
@OperationActivityNumber = scrapModel.OperationActivityNumber,
@Scrapcode = scrapModel.Scrapcode,
@ScrapDescription = scrapModel.ScrapDescription,
@OprnGoodsQty = scrapModel.OprnGoodsQty,
@OprnScrapqty = scrapModel.OprnScrapqty,
@uom = scrapModel.UOM,
@DatePosted = scrapModel.DatePosted,
@PostedBy = scrapModel.PostedBy,
@DateEntered = scrapModel.DateEntered,
@ProdOrderNumb = scrapModel.ProdOrderNumb,
@MaterialNumber = scrapModel.MaterialNumber,
@MaterialDescription = scrapModel.MaterialDescription,
@QtyInOrderUnit = scrapModel.QtyInOrderUnit,
@ScrapinOrderUnit = scrapModel.ScrapinOrderUnit,
@OrderUnit = scrapModel.OrderUnit,
@stdcost = scrapModel.StdCost,
@valuatedstock = scrapModel.Valuatedstock,
@OperScrapCost = scrapModel.OperScrapCost,
@Totoperscrapcost = scrapModel.Totoperscrapcost,
@Prodhierarchy = scrapModel.Prodhierarchy,
@MRPcontroller = scrapModel.MRPcontroller,
@OrderType = scrapModel.OrderType,
@ControlKey = scrapModel.ControlKey,
@ProductLine = scrapModel.ProductLine,
@plant = scrapModel.Plant,
@ConfirmationNumber = scrapModel.ConfirmationNumber,
@ConfirmationCounter = scrapModel.ConfirmationCounter,
@ActualOperator = scrapModel.ActualOperator,
@Productionscheduler = scrapModel.Productionscheduler,
@YieldtoBeConfirmed = scrapModel.YieldtoBeConfirmed,
@ScraptoBeConfirmed = scrapModel.ScraptoBeConfirmed
};
executeProcedure(procedure, values);
}
public int SaveScrap(ScrapModel model)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Parameters.Clear();
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UDSP_SaveScrapProduct";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@UserId", model.UserId);
CommandObj.Parameters.AddWithValue("@BranchId", model.BranchId);
CommandObj.Parameters.AddWithValue("@CompanyId", model.CompanyId);
CommandObj.Parameters.AddWithValue("@TransactionRef", model.TransactionRef);
CommandObj.Parameters.AddWithValue("@TransactionType", model.TransactionType);
CommandObj.Parameters.AddWithValue("@TransactionDate", model.TransactionDate);
CommandObj.Parameters.Add("@MasterId", SqlDbType.BigInt);
CommandObj.Parameters["@MasterId"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
long masterId = Convert.ToInt32(CommandObj.Parameters["@MasterId"].Value);
var rowAffected = SaveScrapProductToScrapInventory(model, masterId);
if (rowAffected > 0)
{
sqlTransaction.Commit();
}
return(rowAffected);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
Log.WriteErrorLog(exception);
throw new Exception("Could not save Scrap product Info", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public List <MessageModel> CrudScrap(ScrapModel scrap)
{
DataTable dtDetails;
dtDetails = ScrapProductDetails(scrap.ScrapProductDetails);
List <MessageModel> response = new List <MessageModel>();
try
{
response = _db.Query <MessageModel>("CRUD_SCRAP_REQUEST",
new
{
P_SCRAPID = scrap.SCRAPID,
P_MODE = scrap.MODE,
P_SCRAPDATE = scrap.SCRAPDATE,
P_USERTYPEID = scrap.USERTYPEID,
P_USERTYPE = scrap.USERTYPE,
P_USERID = scrap.USERID,
P_USERNAME = scrap.USERNAME,
P_BRANCHID = scrap.BRANCHID,
P_CREATEDBY = scrap.CREATEDBY,
P_FINYEAR = scrap.FINYEAR,
P_REMARKS = scrap.REMARKS,
TempTableDetails = dtDetails.AsTableValuedParameter("Type_SCRAP_PRODUCT_DETAILS")
},
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dtDetails = null;
}
return(response);
}
private int SaveScrapProductToScrapInventory(ScrapModel model, long masterId)
{
int i = 0;
int n = 0;
foreach (var item in model.ScannedProducts)
{
CommandObj.CommandText = "UDSP_SaveScrapProductToScrapInventoryDetails";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@ScrapId", masterId);
CommandObj.Parameters.AddWithValue("@ProductCode", item.ProductCode);
CommandObj.Parameters.AddWithValue("@ProductId", Convert.ToInt32(item.ProductCode.Substring(2, 3)));
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
i += Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
}
if (i > 0)
{
n = SaveScrapItemWithQuantity(model, masterId);
}
return(n);
}
public bool Update(ScrapModel model)
{
throw new NotImplementedException();
}
public bool SaveScrap(ScrapModel model)
{
return(_iScrapGateway.SaveScrap(model) > 0);
}
public int Delete(ScrapModel model)
{
throw new NotImplementedException();
}
/// <summary>
/// parse Scrap data and insert data into SQL
/// </summary>
private static void scrap()
{
bool success = true;
int errorCount = 0;
int newLineCount = 0;
string timeStart = DateTime.Now.ToString();
List <string> errors = new List <string>();
string[] lines;
string transaction = "Scrap";
try
{
lines = File.ReadAllLines(fileScrap);
}
catch (Exception e)
{
success = false;
lines = null;
string buildError = "No file found: \n\t" + e.ToString();
errorCount++;
txtWriter.Log(buildError, transaction, errorCount, newLineCount);
}
//define counters for info tracker to count each pass in the foreach function
int countPassedUpdate = 0;
int countInserCommand = 0;
if (success)
{
ScrapModel scrapModel;
foreach (string line in lines)
{
scrapModel = new ScrapModel();
scrapModel.ParseScrap(line);
scrapModel.ID = sqlTransactions.SelectScrapID(scrapModel);
if (scrapModel.ID < 1)
{
try
{
sqlTransactions.InsertScrap(scrapModel);
newLineCount++;
countInserCommand++;
}
catch (Exception ee)
{
txtWriter.Log(ee.ToString(), scrapModel.MaterialNumber);
}
}
else
{
countPassedUpdate++;
}
}
string strgInfo = $"{transaction} started at {timeStart}: \n \tInserts:{countInserCommand}\n\tUpdates:NA\n\tNo Transaction: {countPassedUpdate}\n\n";
txtWriter.writeInfo(strgInfo);
//define streams and send to txtWriter.Log writer
if (errorCount > 0)
{
txtWriter.Log(errors, transaction, errorCount, newLineCount);
}
}
success = true;
}