//public CResult ReadByBranchAndDate(string BranchCode, DateTime FromDate, DateTime ToDate)
//{
// List<CGRDetails> oItemList = new List<CGRDetails>();
// oResult = new CResult();
// conn = oConnManager.GetConnection(out s_DBError);
// if (conn != null)
// {
// try
// {
// DataSet ds = new DataSet();
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = conn;
// cmd.CommandText = "sp_GRDetail_ReadByBrnIdAndDate";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Clear();
// cmd.Parameters.AddWithValue("@BranchID", BranchCode);
// cmd.Parameters.AddWithValue("@SOMstr_DateFrom", FromDate);
// cmd.Parameters.AddWithValue("@SOMstr_DateTo", ToDate);
// SqlDataAdapter da = new SqlDataAdapter(cmd);
// da.Fill(ds);
// DataTable dtItem = ds.Tables[0];
// // oItemList.Add("");
// foreach (DataRow dr in dtItem.Rows)
// {
// CGRDetails oGRDetails = new CGRDetails();
// oGRDetails.GRDet_ItemOID = dr["GRDet_ItemOID"].ToString();
// oGRDetails.GRDet_QTY = float.Parse(dr["GRDet_QTY"].ToString());
// oGRDetails.GRDet_BranchOID = dr["GRDet_Branch"].ToString();
// oGRDetails.GRDet_Price = float.Parse(dr["GRDet_Price"].ToString());
// oGRDetails.GRDet_Amount = float.Parse(dr["GRDet_Amount"].ToString());
// oGRDetails.GRDet_ItemName = dr["GRDet_ItemName"].ToString();
// oItemList.Add(oGRDetails);
// }
// oResult.IsSuccess = true;
// oResult.Data = oItemList;
// }
// catch (SqlException e)
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = e.Message;
// }
// finally
// {
// oConnManager.Close();
// }
// }
// else
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = s_DBError;
// }
// return oResult;
//}
//public CResult ReduceByItemOID(List<CSODetails> oListSODetails)
//{
// oResult = new CResult();
// conn = oConnManager.GetConnection(out s_DBError);
// if (conn != null)
// {
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = conn;
// cmd.Transaction = oConnManager.BeginTransaction();
// try
// {
// foreach (CSODetails oSODetails in oListSODetails)
// {
// cmd.CommandText = "sp_GRDetails_ReduceByItemOID";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Clear();
// cmd.Parameters.AddWithValue("@ItemOID", oSODetails.SODet_ItemOID);
// cmd.Parameters.AddWithValue("@ItemQTY", oSODetails.SODet_QTY);
// cmd.Parameters.AddWithValue("@BranchOID", oSODetails.SODet_BranchOID);
// cmd.ExecuteNonQuery();
// }
// oConnManager.Commit();
// oResult.IsSuccess = true;
// }
// catch (SqlException e)
// {
// string sRollbackError = oConnManager.Rollback();
// oResult.IsSuccess = false;
// oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
// }
// finally
// {
// oConnManager.Close();
// }
// }
// else
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = s_DBError;
// }
// return oResult;
//}
public CResult ExportAndUpdate(CGRMaster oMaster, string url)
{
CInventory oInventory = new CInventory();
CResult oResult = new CResult();
DataTable dt = new DataTable();
List <CInventory> oInvtList = new List <CInventory>();
List <CGRDetails> exportExcelList = new List <CGRDetails>();
oResult = new CResult();
conn = oConnManager.GetConnection(out s_DBError);
if (conn != null)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Parameters.Clear();
cmd.Transaction = oConnManager.BeginTransaction();
try
{
foreach (CGRDetails oDetails in oMaster.GRMstr_DetailsList)
{
oInventory = new CInventory();
oInventory.Invt_Branch = oDetails.GRDet_Branch;
oInventory.Invt_BranchOID = oDetails.GRDet_BranchOID;
oInventory.Invt_InvType = oDetails.GRDet_InvType;
oInventory.Invt_ItemOID = oDetails.GRDet_ItemOID;
oInventory.Invt_LocOID = oDetails.GRDet_LocOID;
oInventory.Invt_QTY = oDetails.GRDet_QTY;
oInvtList.Add(oInventory);
exportExcelList.Add(oDetails);
}
CsvExport <CGRDetails> csv = new CsvExport <CGRDetails>(exportExcelList);
csv.Export();
csv.ExportToFile(url);
// InventoryDT = ConvertToDataTable<CGRDetails>(exportExcelList);
CInventoryBO oInventoryBO = new CInventoryBO();
oResult = oInventoryBO.InvtDec(oInvtList);
if (oResult.IsSuccess)
{
oConnManager.Commit();
oResult.IsSuccess = true;
}
else
{
oResult.ErrMsg = oConnManager.Rollback();
oResult.IsSuccess = false;
}
}
catch (SqlException e)
{
string sRollbackError = oConnManager.Rollback();
oResult.IsSuccess = false;
oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
}
finally
{
oConnManager.Close();
}
}
else
{
oResult.IsSuccess = false;
oResult.ErrMsg = s_DBError;
}
return(oResult);
}
public CResult ReduceByItemOID(List <CSODetails> oListSODetails)
{
List <CInventory> oInvtList = new List <CInventory>();
oResult = new CResult();
conn = oConnManager.GetConnection(out s_DBError);
if (conn != null)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = oConnManager.BeginTransaction();
try
{
foreach (CSODetails oSODetails in oListSODetails)
{
StringBuilder sb = new StringBuilder();
sb.Append("DECLARE @GRDet_OID char(24); ");
sb.Append("DECLARE @GRDet_QTY float; ");
sb.Append("DECLARE @TempQTY float; ");
sb.Append("WHILE (@ItemQTY > 0) BEGIN ");
sb.Append("SELECT @GRDet_QTY = 0; ");
sb.Append("SELECT @TempQTY = 0; ");
sb.Append("SELECT TOP(1) @GRDet_OID = [GRDet_OID], @GRDet_QTY = [GRDet_QTY] ");
sb.Append("FROM t_GRDet ");
sb.Append("WHERE [GRDet_ItemOID] = @ItemOID AND [GRDet_BranchOID] = @BranchOID AND [GRDet_QTY] > 0 ");
sb.Append("IF (@GRDet_OID IS NOT NULL) BEGIN ");
sb.Append("IF(@ItemQTY > @GRDet_QTY) BEGIN ");
sb.Append("SELECT @TempQTY = 0; ");
sb.Append("SELECT @ItemQTY = @ItemQTY - @GRDet_QTY; ");
sb.Append("END ELSE BEGIN ");
sb.Append("SELECT @TempQTY = @GRDet_QTY - @ItemQTY; ");
sb.Append("SELECT @ItemQTY = 0; ");
sb.Append("END ");
sb.Append("UPDATE t_GRDet ");
sb.Append("SET ");
sb.Append("[GRDet_QTY] = @TempQTY ");
sb.Append("WHERE ");
sb.Append("[GRDet_OID] = @GRDet_OID; ");
sb.Append("END ");
sb.Append("SELECT @GRDet_OID = NULL; ");
sb.Append("END");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = sb.ToString();
cmd.Parameters.AddWithValue("@ItemOID", oSODetails.SODet_ItemOID);
cmd.Parameters.AddWithValue("@ItemQTY", oSODetails.SODet_QTY);
cmd.Parameters.AddWithValue("@BranchOID", oSODetails.SODet_BranchOID);
cmd.ExecuteNonQuery();
// populate inv list
CInventory oInventory = new CInventory();
oInventory.Invt_Branch = oSODetails.SODet_Branch;
oInventory.Invt_BranchOID = oSODetails.SODet_BranchOID;
oInventory.Invt_InvType = (int)EInvType.GOOD;
oInventory.Invt_ItemOID = oSODetails.SODet_ItemOID;
oInventory.Invt_LocOID = oSODetails.SODet_LocOID;
oInventory.Invt_QTY = oSODetails.SODet_QTY;
oInvtList.Add(oInventory);
}
//Update inv
CInventoryBO oInventoryBO = new CInventoryBO();
oResult = oInventoryBO.InvtDec(oInvtList);
if (oResult.IsSuccess)
{
oConnManager.Commit();
oResult.IsSuccess = true;
}
else
{
oResult.ErrMsg = oConnManager.Rollback();
oResult.IsSuccess = false;
}
}
catch (SqlException e)
{
string sRollbackError = oConnManager.Rollback();
oResult.IsSuccess = false;
oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
}
finally
{
oConnManager.Close();
}
}
else
{
oResult.IsSuccess = false;
oResult.ErrMsg = s_DBError;
}
return(oResult);
}