public static bool Save(SqlConnection connection, Item_Subclass_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Item_Subclass
(Subclass_Name
,idClass)
VALUES
(@Subclass_Name
,@idClass)");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Subclass_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Subclass_Name
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idClass",
SqlDbType = SqlDbType.Int,
Value = model.idClass
};
cmd.Parameters.Add(parm3);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, Item_Class_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Item_Class SET
Product_Name = @Product_Name
WHERE idClass = @idClass ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idClass",
SqlDbType = SqlDbType.Int,
Value = model.idClass
};
cmd.Parameters.Add(parm1);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Product_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Product_Name
};
cmd.Parameters.Add(parm3);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool UpdatePOStatus(SqlConnection connection, PO_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_PO_Header SET
POStatus = @POStatus
WHERE idPOHeader = @idPOHeader ");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@POStatus",
SqlDbType = SqlDbType.NVarChar,
Value = model.POStatus
};
cmd.Parameters.Add(parm17);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
}
cmd.Dispose();
cmd.Parameters.Clear();
}
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
SQL_Transact.RollbackTransaction(connection, GUID);
}
return(returnValue);
}
public static bool Delete(SqlConnection connection, int idSTHeader)
{
bool boolReturnValue = false;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append("DELETE FROM a_Stock_Transfer_Detail ");
sQuery.Append(@"WHERE idSTHeader = @idSTHeader ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter
{
ParameterName = "@idSTHeader",
SqlDbType = SqlDbType.Int,
Value = idSTHeader
};
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() >= 1)
{
boolReturnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(boolReturnValue);
}
public static bool Delete(SqlConnection connection, string credit_term)
{
bool boolReturnValue = false;
StringBuilder sQuery = new StringBuilder();
sQuery.Append("DELETE FROM a_Credit_Term ");
sQuery.Append("WHERE credit_term = @credit_term");
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = credit_term
};
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() >= 1)
{
boolReturnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(boolReturnValue);
}
public static bool Update(SqlConnection connection, Stock_Transfer_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Stock_Transfer_Header SET
Doc_Number = @Doc_Number
,STR_Number = @STR_Number
,idCustomer = @idCustomer
,Site_From = @Site_From
,Site_To = @Site_To
,timestamp = @timestamp
WHERE idSTHeader = @idSTHeader
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idSTHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSTHeader
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Doc_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Doc_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@STR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.STR_Number
};
cmd.Parameters.Add(parm3);
SqlParameter parm5 = new SqlParameter();
parm5.ParameterName = "@Site_From";
parm5.SqlDbType = SqlDbType.NVarChar;
parm5.Value = model.Site_From;
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Site_To",
SqlDbType = SqlDbType.DateTime,
Value = model.Site_To
};
cmd.Parameters.Add(parm6);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@timestamp",
SqlDbType = SqlDbType.NVarChar,
Value = model.timestamp
};
cmd.Parameters.Add(parm8);
try
{
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, Return_Header_Model model)
{
int returnValue = 0;
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Return_Header
(SLR_Number
,SO_Number
,Customer_Code
,Auth_Number
,isReplacement
,Document_Number
,Site
,Reason_Code
,Date_Returned
,Remarks
)
VALUES
(@SLR_Number
,@SO_Number
,@Customer_Code
,@Auth_Number
,@isReplacement
,@Document_Number
,@Site
,@Reason_Code
,@Date_Returned
,@Remarks
)
SELECT SCOPE_IDENTITY() as 'ID'
");
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@SLR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.SLR_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@SO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.SO_Number
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Customer_Code",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Code
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Auth_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Auth_Number
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@isReplacement",
SqlDbType = SqlDbType.NVarChar,
Value = model.isReplacement
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Document_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Document_Number
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Site",
SqlDbType = SqlDbType.Int,
Value = model.Site
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Reason_Code",
SqlDbType = SqlDbType.NVarChar,
Value = model.Reason_Code
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@Date_Returned",
SqlDbType = SqlDbType.DateTime,
Value = model.Date_Returned
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm11);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = 0;
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, Import_Shipment_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Import_Shipment_Header
SET ImportShipmentNumber = @ImportShipmentNumber
,Brokerage = @Brokerage
,CEDEC = @CEDEC
,CustomsStamps = @CustomsStamps
,DeliveryCharges = @DeliveryCharges
,DocumentaryStamps = @DocumentaryStamps
,DocumentationCharges = @DocumentationCharges
,ForkliftCost = @ForkliftCost
,FreightCharges = @FreightCharges
,HandlingFee = @HandlingFee
,ImportDuties = @ImportDuties
,ImportProcessingFee = @ImportProcessingFee
,ImportationInsurance = @ImportationInsurance
,Miscellaneous = @Miscellaneous
,NotarialFee = @NotarialFee
,OtherCharges = @OtherCharges
,ProcessingFee = @ProcessingFee
,WarehouseStorageCharges = @WarehouseStorageCharges
,Xerox = @Xerox
,Total_Charges = @Total_Charges
WHERE idImpShpHead = @idImpShpHead ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idImpShpHead",
SqlDbType = SqlDbType.Int,
Value = model.idImpShpHead
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@ImportShipmentNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.ImportShipmentNumber
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Brokerage",
SqlDbType = SqlDbType.Decimal,
Value = model.Brokerage
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@CEDEC",
SqlDbType = SqlDbType.Decimal,
Value = model.CEDEC
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@CustomsStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.CustomsStamps
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@DeliveryCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DeliveryCharges
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@DocumentaryStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentaryStamps
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@DocumentationCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentationCharges
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@ForkliftCost",
SqlDbType = SqlDbType.Decimal,
Value = model.ForkliftCost
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@FreightCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.FreightCharges
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@HandlingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.HandlingFee
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@ImportDuties",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportDuties
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@ImportProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportProcessingFee
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@ImportationInsurance",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportationInsurance
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Miscellaneous",
SqlDbType = SqlDbType.Decimal,
Value = model.Miscellaneous
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@NotarialFee",
SqlDbType = SqlDbType.Decimal,
Value = model.NotarialFee
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@OtherCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.OtherCharges
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@ProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ProcessingFee
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@WarehouseStorageCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.WarehouseStorageCharges
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@Xerox",
SqlDbType = SqlDbType.Decimal,
Value = model.Xerox
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Total_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Total_Charges
};
cmd.Parameters.Add(parm21);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Save(SqlConnection connection, SO_Detail_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_SO_Detail
(idSOHeader
,idItem
,Qty
,Cost
,UM
,Discount
,Tax_Amount
,Amount)
VALUES
(@idSOHeader
,@idItem
,@Qty
,@Cost
,@UM
,@Discount
,@Tax_Amount
,@Amount)
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idItem",
SqlDbType = SqlDbType.Int,
Value = model.idItem
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Qty",
SqlDbType = SqlDbType.Int,
Value = model.Qty
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Cost",
SqlDbType = SqlDbType.Decimal,
Value = model.Cost
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@UM",
SqlDbType = SqlDbType.NVarChar,
Value = model.UM
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Discount",
SqlDbType = SqlDbType.Decimal,
Value = model.Discount
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Tax_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Tax_Amount
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Amount
};
cmd.Parameters.Add(parm9);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Save(SqlConnection connection, Logs_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Logs
(idUser
,Description
,Form)
VALUES
(@idUser
,@Description
,@Form)
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idUser",
SqlDbType = SqlDbType.Int,
Value = model.idUser
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Description",
SqlDbType = SqlDbType.NVarChar,
Value = model.Description
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Form",
SqlDbType = SqlDbType.NVarChar,
Value = model.Form
};
cmd.Parameters.Add(parm3);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
//public static bool UpdateSOStatus(SqlConnection connection, SO_Header_Model model)
//{
// bool returnValue = true;
// StringBuilder sQuery = new StringBuilder();
// try
// {
// sQuery.Append(@"UPDATE a_SO_Header SET
// SO_Status = @SO_Status
// WHERE idSOHeader = @idSOHeader ");
// connection.Open();
// using (SqlCommand cmd = new SqlCommand())
// {
// cmd.Connection = connection;
// cmd.CommandText = sQuery.ToString();
// cmd.CommandType = CommandType.Text;
// SqlParameter parm1 = new SqlParameter
// {
// ParameterName = "@idSOHeader",
// SqlDbType = SqlDbType.Int,
// Value = model.idSOHeader
// };
// cmd.Parameters.Add(parm1);
// SqlParameter parm2 = new SqlParameter
// {
// ParameterName = "@SO_Status",
// SqlDbType = SqlDbType.NVarChar,
// Value = model.SO_Status
// };
// cmd.Parameters.Add(parm2);
// if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
// cmd.Dispose();
// cmd.Parameters.Clear();
// }
// connection.Close();
// }
// catch (Exception ex)
// {
// throw new ArgumentException(ex.Message);
// }
// return returnValue;
//}
public static bool Delete(SqlConnection connection, int idSOHeader)
{
bool boolReturnValue = false;
var GUID = SQL_Transact.GenerateGUID();
StringBuilder sQuery = new StringBuilder();
sQuery.Append(" UPDATE a_SO_Header SET Active = 'N' , SO_Status = 'DELETED' ");
sQuery.Append(" WHERE idSOHeader = @idSOHeader ");
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = idSOHeader
};
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() >= 1)
{
boolReturnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
SqlConnection con2 = new SqlConnection(connection.ConnectionString);
sQuery = new StringBuilder();
sQuery.Append(@"SELECT idSODetail
,idSOHeader
,idItem
,Qty
,Cost
,UM
,Discount
,Amount
FROM a_SO_Detail WHERE idSODetail <> 0
");
if (idSOHeader != 0)
{
sQuery.Append(" AND idSOHeader = @idSOHeader ");
}
var lmodel = new List <SO_Detail_Model>();
con2.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con2;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
if (idSOHeader != 0)
{
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = idSOHeader
};
cmd.Parameters.Add(parm2);
}
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
SO_Detail_Model oModel = new SO_Detail_Model
{
idSODetail = (int)oreader["idSODetail"],
idSOHeader = (int)oreader["idSOHeader"],
idItem = (int)oreader["idItem"],
Qty = (int)oreader["Qty"],
Cost = (Decimal)oreader["Cost"],
UM = (string)oreader["UM"],
Discount = (Decimal)oreader["Discount"],
Amount = (Decimal)oreader["Amount"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
con2.Close();
for (int x = 0; x <= lmodel.Count - 1; x++)
{
int soqty = decimal.ToInt32(lmodel[x].Qty);
var itemModel = Item_Master.RetreiveAllocation(con2, lmodel[x].idItem);
int master_alloc = Convert.ToInt32(itemModel.Rows[0][2].ToString());
int master_total = Convert.ToInt32(itemModel.Rows[0][3].ToString());
int newAlloc = master_alloc - soqty;
int newTotal = master_total + soqty;
Item_Master_Model mastermodel = new Item_Master_Model();
mastermodel.Alloc_Qty = newAlloc;
mastermodel.Total_Qty = newTotal;
mastermodel.idItem = lmodel[x].idItem;
Item_Master.UpdateQtyOnly(con2, mastermodel);
boolReturnValue = true;
}
return(boolReturnValue);
}
public static bool Save(SqlConnection connection, Pick_Serial_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Pick_Serial
(idPickDetail
,idSerial
,Serial_No
,PO_Number
)
VALUES
(@idPickDetail
,@idSerial
,@Serial_No
,@PO_Number
)
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPickDetail",
SqlDbType = SqlDbType.Int,
Value = model.idPickDetail
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idSerial",
SqlDbType = SqlDbType.Int,
Value = model.idSerial
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Serial_No",
SqlDbType = SqlDbType.NVarChar,
Value = model.Serial_No
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@PO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.PO_Number
};
cmd.Parameters.Add(parm5);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Save(SqlConnection connection, Credit_Term_Model model)
{
bool returnValue = true;
StringBuilder sQuery = new StringBuilder();
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
sQuery.Append(@"INSERT INTO a_Credit_Term
(credit_term
,term_desc
,days_to_pay)
VALUES
(@credit_term
,@term_desc
,@days_to_pay)" );
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = model.credit_term
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@term_desc",
SqlDbType = SqlDbType.NVarChar,
Value = model.term_desc
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@days_to_pay",
SqlDbType = SqlDbType.NVarChar,
Value = model.days_to_pay
};
cmd.Parameters.Add(parm4);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, MIR_Header_Model model)
{
int returnValue = 0;
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_MIR_Header
(RequestNo
,Requestor
,ReferenceNo
,POCMNumber
,RequestDate
,PreparedBy
,Remarks
)
VALUES
(@RequestNo
,@Requestor
,@ReferenceNo
,@POCMNumber
,@RequestDate
,@PreparedBy
,@Remarks
)
SELECT SCOPE_IDENTITY() as 'ID'
");
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@RequestNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.RequestNo
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Requestor",
SqlDbType = SqlDbType.NVarChar,
Value = model.Requestor
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@ReferenceNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.ReferenceNo
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@POCMNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.POCMNumber
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@RequestDate",
SqlDbType = SqlDbType.DateTime,
Value = model.RequestDate
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@PreparedBy",
SqlDbType = SqlDbType.NVarChar,
Value = model.PreparedBy
};
cmd.Parameters.Add(parm7);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm11);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = 0;
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, Pick_Header_Model model)
{
int returnValue = 0;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Pick_Header
(idSOHeader
,Pick_Number
,OrderDate
,DueDate
,CustomerCode
,CustPONum
,user_id_chg_by
,Pick_Date
)
VALUES
(@idSOHeader
,@Pick_Number
,@OrderDate
,@DueDate
,@CustomerCode
,@CustPONum
,@user_id_chg_by
,@Pick_Date
)
SELECT SCOPE_IDENTITY() as 'ID'
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Pick_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Pick_Number
};
cmd.Parameters.Add(parm3);
//SqlParameter parm4 = new SqlParameter
//{
// ParameterName = "@idCustomer",
// SqlDbType = SqlDbType.Int,
// Value = model.idCustomer
//};
//cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter();
parm5.ParameterName = "@CustPONum";
parm5.SqlDbType = SqlDbType.NVarChar;
parm5.Value = model.CustPONum;
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@OrderDate",
SqlDbType = SqlDbType.DateTime,
Value = model.OrderDate
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@DueDate",
SqlDbType = SqlDbType.DateTime,
Value = model.DueDate
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@CustomerCode",
SqlDbType = SqlDbType.NVarChar,
Value = model.CustomerCode
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@user_id_chg_by",
SqlDbType = SqlDbType.NVarChar,
Value = model.user_id_chg_by
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@Pick_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Pick_Date
};
cmd.Parameters.Add(parm10);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Save(SqlConnection connection, PO_Charges_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_PO_Charges
(idPOHeader
,Brokerage
,CEDEC
,CustomsStamps
,DeliveryCharges
,DocumentaryStamps
,DocumentationCharges
,ForkliftCost
,FreightCharges
,HandlingFee
,ImportDuties
,ImportProcessingFee
,ImportationInsurance
,Miscellaneous
,NotarialFee
,OtherCharges
,ProcessingFee
,WarehouseStorageCharges
,Xerox)
VALUES
(@idPOHeader
,@Brokerage
,@CEDEC
,@CustomsStamps
,@DeliveryCharges
,@DocumentaryStamps
,@DocumentationCharges
,@ForkliftCost
,@FreightCharges
,@HandlingFee
,@ImportDuties
,@ImportProcessingFee
,@ImportationInsurance
,@Miscellaneous
,@NotarialFee
,@OtherCharges
,@ProcessingFee
,@WarehouseStorageCharges
,@Xerox)
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Brokerage",
SqlDbType = SqlDbType.Decimal,
Value = model.Brokerage
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@CEDEC",
SqlDbType = SqlDbType.Decimal,
Value = model.CEDEC
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@CustomsStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.CustomsStamps
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@DeliveryCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DeliveryCharges
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@DocumentaryStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentaryStamps
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@DocumentationCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentationCharges
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@ForkliftCost",
SqlDbType = SqlDbType.Decimal,
Value = model.ForkliftCost
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@FreightCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.FreightCharges
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@HandlingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.HandlingFee
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@ImportDuties",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportDuties
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@ImportProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportProcessingFee
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@ImportationInsurance",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportationInsurance
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Miscellaneous",
SqlDbType = SqlDbType.Decimal,
Value = model.Miscellaneous
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@NotarialFee",
SqlDbType = SqlDbType.Decimal,
Value = model.NotarialFee
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@OtherCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.OtherCharges
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@ProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ProcessingFee
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@WarehouseStorageCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.WarehouseStorageCharges
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@Xerox",
SqlDbType = SqlDbType.Decimal,
Value = model.Xerox
};
cmd.Parameters.Add(parm20);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool UpdateReceivedStatus(SqlConnection connection, PO_Detail_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_PO_Detail SET
isReceived = @isReceived
,Partial_Remaining = @Partial_Remaining
WHERE idPODetail = @idPODetail ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPODetail",
SqlDbType = SqlDbType.Int,
Value = model.idPODetail
};
cmd.Parameters.Add(parm2);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@isReceived",
SqlDbType = SqlDbType.NVarChar,
Value = model.isReceived
};
cmd.Parameters.Add(parm17);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Partial_Remaining",
SqlDbType = SqlDbType.Decimal,
Value = model.Partial_Remaining
};
cmd.Parameters.Add(parm11);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, PO_Detail_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_PO_Detail SET
idPOHeader = @idPOHeader
,idItem = @idItem
,Quantity = @Quantity
,Price = @Price
,Tax = @Tax
,Amount = @Amount
,Final_Cost = @Final_Cost
,Unit_Comp = @Unit_Comp
,isReceived = @isReceived
,Partial_Remaining = @Partial_Remaining
WHERE idPODetail = @idPODetail ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idPODetail",
SqlDbType = SqlDbType.Int,
Value = model.idPODetail
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Price",
SqlDbType = SqlDbType.Decimal,
Value = model.Price
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@idItem",
SqlDbType = SqlDbType.Int,
Value = model.idItem
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@isReceived",
SqlDbType = SqlDbType.NVarChar,
Value = model.isReceived
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Quantity",
SqlDbType = SqlDbType.Decimal,
Value = model.Quantity
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Unit_Comp",
SqlDbType = SqlDbType.NVarChar,
Value = model.Unit_Comp
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Tax",
SqlDbType = SqlDbType.Decimal,
Value = model.Tax
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Amount
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@Final_Cost",
SqlDbType = SqlDbType.Decimal,
Value = model.Final_Cost
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Partial_Remaining",
SqlDbType = SqlDbType.Decimal,
Value = model.Partial_Remaining
};
cmd.Parameters.Add(parm11);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Delete(SqlConnection connection, int idSTHeader)
{
bool boolReturnValue = false;
var GUID = SQL_Transact.GenerateGUID();
StringBuilder sQuery = new StringBuilder();
sQuery.Append(" UPDATE a_Stock_Transfer_Header SET Active = 'N' , SO_Status = 'DELETED' ");
sQuery.Append(" WHERE idSTHeader = @idSTHeader ");
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm = new SqlParameter
{
ParameterName = "@idSTHeader",
SqlDbType = SqlDbType.Int,
Value = idSTHeader
};
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() >= 1)
{
boolReturnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
SqlConnection con2 = new SqlConnection(connection.ConnectionString);
sQuery = new StringBuilder();
sQuery.Append(@"SELECT idSODetail
,idSTHeader
,idItem
,Cost
,UM
,Discount
,Amount
FROM a_SO_Detail WHERE idSODetail <> 0
");
if (idSTHeader != 0)
{
sQuery.Append(" AND idSTHeader = @idSTHeader ");
}
return(boolReturnValue);
}
public static int Save(SqlConnection connection, PO_Header_Model model)
{
int returnValue = 0;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_PO_Header
(PO_Number
,PO_Date
,Delivery_Date
,Terms
,FOB_Point
,Shipping_Via
,Currency
,idSupplier
,PO_Total
,PO_Quantity
,Total_Charges
,Forex_Rate
,PR_Number
,Created_By
,Remarks
,POStatus
,ImportShipmentNumber)
VALUES
(@PO_Number
,@PO_Date
,@Delivery_Date
,@Terms
,@FOB_Point
,@Shipping_Via
,@Currency
,@idSupplier
,@PO_Total
,@PO_Quantity
,@Total_Charges
,@Forex_Rate
,@PR_Number
,@Created_By
,@Remarks
,@POStatus
,@ImportShipmentNumber)
SELECT SCOPE_IDENTITY() as 'ID'");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@PO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.PO_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idSupplier",
SqlDbType = SqlDbType.Int,
Value = model.idSupplier
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@PO_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.PO_Date
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Delivery_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Delivery_Date
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Terms",
SqlDbType = SqlDbType.NVarChar,
Value = model.Terms
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@FOB_Point",
SqlDbType = SqlDbType.NVarChar,
Value = model.FOB_Point
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Shipping_Via",
SqlDbType = SqlDbType.NVarChar,
Value = model.Shipping_Via
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Currency",
SqlDbType = SqlDbType.NVarChar,
Value = model.Currency
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@PO_Total",
SqlDbType = SqlDbType.Decimal,
Value = model.PO_Total
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@PO_Quantity",
SqlDbType = SqlDbType.Int,
Value = model.PO_Quantity
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@Total_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Total_Charges
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@Forex_Rate",
SqlDbType = SqlDbType.Decimal,
Value = model.Forex_Rate
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@PR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.PR_Number
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Created_By",
SqlDbType = SqlDbType.NVarChar,
Value = model.Created_By
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@POStatus",
SqlDbType = SqlDbType.NVarChar,
Value = model.POStatus
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@ImportShipmentNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.ImportShipmentNumber
};
cmd.Parameters.Add(parm18);
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, Stock_Transfer_Header_Model model)
{
int returnValue = 9;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Stock_Transfer_Header
(Doc_Number
,STR_Number
,Site_From
,Site_To
,timestamp
)
VALUES
(@Doc_Number
,@STR_Number
,@Site_From
,@Site_To
,@timestamp
)
SELECT SCOPE_IDENTITY() as 'ID'
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Doc_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Doc_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@STR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.STR_Number
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Site_From",
SqlDbType = SqlDbType.NVarChar,
Value = model.Site_From
};
cmd.Parameters.Add(parm4);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Site_To",
SqlDbType = SqlDbType.NVarChar,
Value = model.Site_To
};
cmd.Parameters.Add(parm6);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@timestamp",
SqlDbType = SqlDbType.DateTime,
Value = model.timestamp
};
cmd.Parameters.Add(parm8);
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = 0;
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool UpdateStatus(SqlConnection connection, string Pick_Status, string SO_Status, string Active, int idSOHeader)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_SO_Header SET
Pick_Status = @Pick_Status,
SO_Status = @SO_Status,
Active = @Active
WHERE idSOHeader = @idSOHeader
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = idSOHeader
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Pick_Status",
SqlDbType = SqlDbType.NChar,
Value = Pick_Status
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@SO_Status",
SqlDbType = SqlDbType.NVarChar,
Value = SO_Status
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Active",
SqlDbType = SqlDbType.NVarChar,
Value = Active
};
cmd.Parameters.Add(parm4);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, Customer_Details_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Customer_Details SET
Customer_Code = @Customer_Code
,Customer_Name = @Customer_Name
,Address1 = @Address1
,Address2 = @Address2
,Address3 = @Address3
,Address4 = @Address4
,credit_term = @credit_term
,Company_Name = @Company_Name
,Status = @Status
,AddressShipping1 = @AddressShipping1
,AddressShipping2 = @AddressShipping2
,AddressShipping3 = @AddressShipping3
,AddressShipping4 = @AddressShipping4
,user_chg_by = @user_chg_by
,Customer_Type = @Customer_Type
,Credit_Limit = @Credit_Limit
,Position = @Position
,Contact_Number = @Contact_Number
,TIN_Number = @TIN_Number
,Salesman = @Salesman
,Email_Address = @Email_Address
WHERE idCustomer = @idCustomer ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idCustomer",
SqlDbType = SqlDbType.Int,
Value = model.idCustomer
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Customer_Code",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Code
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Customer_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Name
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Address1",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address1
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Address2",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address2
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Address3",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address3
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Address4",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address4
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = model.credit_term
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Company_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Company_Name
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@Status",
SqlDbType = SqlDbType.NVarChar,
Value = model.Status
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@AddressShipping1",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping1
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@AddressShipping2",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping2
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@AddressShipping3",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping3
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@AddressShipping4",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping4
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@user_chg_by",
SqlDbType = SqlDbType.NVarChar,
Value = model.user_chg_by
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Customer_Type",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Type
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@Credit_Limit",
SqlDbType = SqlDbType.NVarChar,
Value = model.Credit_Limit
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@Position",
SqlDbType = SqlDbType.NVarChar,
Value = model.Position
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@Contact_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Contact_Number
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@TIN_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.TIN_Number
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Salesman",
SqlDbType = SqlDbType.NVarChar,
Value = model.Salesman
};
cmd.Parameters.Add(parm21);
SqlParameter parm22 = new SqlParameter
{
ParameterName = "@Email_Address",
SqlDbType = SqlDbType.NVarChar,
Value = model.Email_Address
};
cmd.Parameters.Add(parm22);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, SO_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_SO_Header SET
SO_Number = @SO_Number
,idPOHeader = @idPOHeader
,idCustomer = @idCustomer
,Due_Date = @Due_Date
,Customer_PO = @Customer_PO
,Salesman = @Salesman
,Ship_Code = @Ship_Code
,Gross_Amount = @Gross_Amount
,Final_Discount = @Final_Discount
,Freight_Charges = @Freight_Charges
,Other_Charges = @Other_Charges
,Net_Amount = @Net_Amount
,Tax_Amount = @Tax_Amount
,credit_term = @credit_term
,Remarks = @Remarks
,idSite = @idSite
,currency_code = @currency_code
,Special_Concession = @Special_Concession
,Stock_Status = @Stock_Status
,End_User = @End_User
,End_User_City = @End_User_City
,Forex_Rate = @Forex_Rate
WHERE idSOHeader = @idSOHeader
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSOHeader
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@SO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.SO_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@idCustomer",
SqlDbType = SqlDbType.Int,
Value = model.idCustomer
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter();
parm5.ParameterName = "@Forex_Rate";
parm5.SqlDbType = SqlDbType.Decimal;
parm5.Value = model.Forex_Rate;
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Due_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Due_Date
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Customer_PO",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_PO
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Salesman",
SqlDbType = SqlDbType.NVarChar,
Value = model.Salesman
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Ship_Code",
SqlDbType = SqlDbType.Int,
Value = model.Ship_Code
};
cmd.Parameters.Add(parm9);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Gross_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Gross_Amount
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@Final_Discount",
SqlDbType = SqlDbType.Decimal,
Value = model.Final_Discount
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@Freight_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Freight_Charges
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@Other_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Other_Charges
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Net_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Net_Amount
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Tax_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Tax_Amount
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = model.credit_term
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@idSite",
SqlDbType = SqlDbType.Int,
Value = model.idSite
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@currency_code",
SqlDbType = SqlDbType.NVarChar,
Value = model.currency_code
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Special_Concession",
SqlDbType = SqlDbType.NVarChar,
Value = model.Special_Concession
};
cmd.Parameters.Add(parm21);
SqlParameter parm22 = new SqlParameter
{
ParameterName = "@Stock_Status",
SqlDbType = SqlDbType.NVarChar,
Value = model.Stock_Status
};
cmd.Parameters.Add(parm22);
SqlParameter parm25 = new SqlParameter
{
ParameterName = "@End_User",
SqlDbType = SqlDbType.NVarChar,
Value = model.End_User
};
cmd.Parameters.Add(parm25);
SqlParameter parm26 = new SqlParameter
{
ParameterName = "@End_User_City",
SqlDbType = SqlDbType.NVarChar,
Value = model.End_User_City
};
cmd.Parameters.Add(parm26);
try
{
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Save(SqlConnection connection, Invoice_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"
INSERT INTO dbo.a_Invoice
(Invoice_Number
,idSOHeader
,Invoice_Date
,DR_Number
,Amount
,Del_Date
,OR_Number)
VALUES
(@Invoice_Number
,@idSOHeader
,@Invoice_Date
,@DR_Number
,@Amount
,@Del_Date
,@OR_Number)
");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@Invoice_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Invoice_Number
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Invoice_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Invoice_Date
};
cmd.Parameters.Add(parm3);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Del_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Del_Date
};
cmd.Parameters.Add(parm6);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@DR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.DR_Number
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Amount
};
cmd.Parameters.Add(parm5);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@OR_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.OR_Number
};
cmd.Parameters.Add(parm7);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, Import_Shipment_Header_Model model)
{
int returnValue = 0;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Import_Shipment_Header
(ImportShipmentNumber
,Brokerage
,CEDEC
,CustomsStamps
,DeliveryCharges
,DocumentaryStamps
,DocumentationCharges
,ForkliftCost
,FreightCharges
,HandlingFee
,ImportDuties
,ImportProcessingFee
,ImportationInsurance
,Miscellaneous
,NotarialFee
,OtherCharges
,ProcessingFee
,WarehouseStorageCharges
,Xerox
,Total_Charges)
VALUES
(@ImportShipmentNumber
,@Brokerage
,@CEDEC
,@CustomsStamps
,@DeliveryCharges
,@DocumentaryStamps
,@DocumentationCharges
,@ForkliftCost
,@FreightCharges
,@HandlingFee
,@ImportDuties
,@ImportProcessingFee
,@ImportationInsurance
,@Miscellaneous
,@NotarialFee
,@OtherCharges
,@ProcessingFee
,@WarehouseStorageCharges
,@Xerox
,@Total_Charges)
SELECT SCOPE_IDENTITY() as 'ID'");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@ImportShipmentNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.ImportShipmentNumber
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Brokerage",
SqlDbType = SqlDbType.Decimal,
Value = model.Brokerage
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@CEDEC",
SqlDbType = SqlDbType.Decimal,
Value = model.CEDEC
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@CustomsStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.CustomsStamps
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@DeliveryCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DeliveryCharges
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@DocumentaryStamps",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentaryStamps
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@DocumentationCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.DocumentationCharges
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@ForkliftCost",
SqlDbType = SqlDbType.Decimal,
Value = model.ForkliftCost
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@FreightCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.FreightCharges
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@HandlingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.HandlingFee
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@ImportDuties",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportDuties
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@ImportProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportProcessingFee
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@ImportationInsurance",
SqlDbType = SqlDbType.Decimal,
Value = model.ImportationInsurance
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Miscellaneous",
SqlDbType = SqlDbType.Decimal,
Value = model.Miscellaneous
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@NotarialFee",
SqlDbType = SqlDbType.Decimal,
Value = model.NotarialFee
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@OtherCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.OtherCharges
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@ProcessingFee",
SqlDbType = SqlDbType.Decimal,
Value = model.ProcessingFee
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@WarehouseStorageCharges",
SqlDbType = SqlDbType.Decimal,
Value = model.WarehouseStorageCharges
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@Xerox",
SqlDbType = SqlDbType.Decimal,
Value = model.Xerox
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Total_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Total_Charges
};
cmd.Parameters.Add(parm21);
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, PO_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_PO_Header SET
PO_Number = @PO_Number
,Terms = @Terms
,FOB_Point = @FOB_Point
,Shipping_Via = @Shipping_Via
,Currency = @Currency
,idSupplier = @idSupplier
,PO_Total = @PO_Total
,PO_Quantity = @PO_Quantity
,Total_Charges = @Total_Charges
,Forex_Rate = @Forex_Rate
,Created_By = @Created_By
,Remarks = @Remarks
,POStatus = @POStatus
,ImportShipmentNumber = @ImportShipmentNumber
WHERE idPOHeader = @idPOHeader ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@PO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.PO_Number
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idSupplier",
SqlDbType = SqlDbType.NVarChar,
Value = model.idSupplier
};
cmd.Parameters.Add(parm3);
//SqlParameter parm4 = new SqlParameter
//{
// ParameterName = "@PO_Date",
// SqlDbType = SqlDbType.DateTime,
// Value = model.PO_Date
//};
//cmd.Parameters.Add(parm4);
//SqlParameter parm5 = new SqlParameter
//{
// ParameterName = "@Delivery_Date",
// SqlDbType = SqlDbType.DateTime,
// Value = model.Delivery_Date
//};
//cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Terms",
SqlDbType = SqlDbType.NVarChar,
Value = model.Terms
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@FOB_Point",
SqlDbType = SqlDbType.NVarChar,
Value = model.FOB_Point
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Shipping_Via",
SqlDbType = SqlDbType.NVarChar,
Value = model.Shipping_Via
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Currency",
SqlDbType = SqlDbType.NVarChar,
Value = model.Currency
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@PO_Total",
SqlDbType = SqlDbType.Decimal,
Value = model.PO_Total
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@PO_Quantity",
SqlDbType = SqlDbType.Int,
Value = model.PO_Quantity
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@Total_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Total_Charges
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@Forex_Rate",
SqlDbType = SqlDbType.Decimal,
Value = model.Forex_Rate
};
cmd.Parameters.Add(parm13);
//SqlParameter parm14 = new SqlParameter
//{
// ParameterName = "@PR_Number",
// SqlDbType = SqlDbType.NVarChar,
// Value = model.PR_Number
//};
//cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Created_By",
SqlDbType = SqlDbType.NVarChar,
Value = model.Created_By
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@POStatus",
SqlDbType = SqlDbType.NVarChar,
Value = model.POStatus
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@ImportShipmentNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.ImportShipmentNumber
};
cmd.Parameters.Add(parm18);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, Pick_Detail_Model model)
{
int returnValue = 0;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Pick_Detail
(idPickHeader
,idItem
,Item_Number
,Description
,Qty
,Items_Picked
)
VALUES
(@idPickHeader
,@idItem
,@Item_Number
,@Description
,@Qty
,@Items_Picked
)
SELECT SCOPE_IDENTITY() as 'ID'
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idPickHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPickHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idItem",
SqlDbType = SqlDbType.Int,
Value = model.idItem
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Item_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Item_Number
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Description",
SqlDbType = SqlDbType.NVarChar,
Value = model.Description
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Qty",
SqlDbType = SqlDbType.Int,
Value = model.Qty
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Items_Picked",
SqlDbType = SqlDbType.Int,
Value = model.Items_Picked
};
cmd.Parameters.Add(parm7);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = 0;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static int Save(SqlConnection connection, MIS_Detail_Model model)
{
int returnValue = 0;
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_MIS_Detail
(idMISHeader
,idItem
,Quantity
,Cost
)
VALUES
(@idMISHeader
,@idItem
,@Quantity
,@Cost
)
SELECT SCOPE_IDENTITY() as 'ID'
");
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idMISHeader",
SqlDbType = SqlDbType.Int,
Value = model.idMISHeader
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idItem",
SqlDbType = SqlDbType.Int,
Value = model.idItem
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Quantity",
SqlDbType = SqlDbType.Int,
Value = model.Quantity
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Cost",
SqlDbType = SqlDbType.Decimal,
Value = model.Cost
};
cmd.Parameters.Add(parm5);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = 0;
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, Import_Shipment_Detail_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Import_Shipment_Detail SET
idImpShpHead = @idImpShpHead
,idPOHeader = @idPOHeader
,POCharge = @POCharge
WHERE idImpShpDet = @idImpShpDet ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idImpShpDet",
SqlDbType = SqlDbType.Int,
Value = model.idImpShpDet
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idImpShpHead",
SqlDbType = SqlDbType.Int,
Value = model.idImpShpHead
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@POCharge",
SqlDbType = SqlDbType.Decimal,
Value = model.POCharge
};
cmd.Parameters.Add(parm4);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}