internal static StoredProcedure GenerateDocumentCode(string transactionType)
{
//TODO : XML config for SP name
StoredProcedure sp = new StoredProcedure("GenerateDocumentCode");
sp.AddParameter("@TransactionType", StoredProcedure.ParameterType.String, 30, transactionType);
return sp;
}
public StoredProcedure aspnet_Membership_GetNumberOfUsersOnline(string ApplicationName,int MinutesSinceLastInActive,DateTime CurrentTimeUtc){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetNumberOfUsersOnline",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("MinutesSinceLastInActive",MinutesSinceLastInActive,DbType.Int32);
sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
return sp;
}
public StoredProcedure DeleteBoardContributor(string Email,int BoardID)
{
StoredProcedure sp=new StoredProcedure("DeleteBoardContributor",this.Provider);
sp.Command.AddParameter("Email",Email,DbType.AnsiString);
sp.Command.AddParameter("BoardID",BoardID,DbType.Int32);
return sp;
}
public StoredProcedure AddBoardContributor(string User,int BoardID)
{
StoredProcedure sp=new StoredProcedure("AddBoardContributor",this.Provider);
sp.Command.AddParameter("User",User,DbType.AnsiString);
sp.Command.AddParameter("BoardID",BoardID,DbType.Int32);
return sp;
}
public StoredProcedure UpdatePoints(int UserID,string PointsName)
{
StoredProcedure sp=new StoredProcedure("UpdatePoints",this.Provider);
sp.Command.AddParameter("UserID",UserID,DbType.Int32);
sp.Command.AddParameter("PointsName",PointsName,DbType.AnsiString);
return sp;
}
public StoredProcedure DeletePin(int BIMID,int UserID)
{
StoredProcedure sp=new StoredProcedure("DeletePin",this.Provider);
sp.Command.AddParameter("BIMID",BIMID,DbType.Int32);
sp.Command.AddParameter("UserID",UserID,DbType.Int32);
return sp;
}
public StoredProcedure UpdatePrize(int UserID,int RWPointDed)
{
StoredProcedure sp=new StoredProcedure("UpdatePrize",this.Provider);
sp.Command.AddParameter("UserID",UserID,DbType.Int32);
sp.Command.AddParameter("RWPointDed",RWPointDed,DbType.Int32);
return sp;
}
private StoredProcedure BaoCaoSoTruc(DateTime? pFromDate, DateTime? pToDate)
{
var sp = new StoredProcedure("GTVT_BAOCAO_SOTRUC", DataService.GetInstance("ORM"), "dbo");
sp.Command.AddParameter("@FromDate", pFromDate, DbType.DateTime, null, null);
sp.Command.AddParameter("@ToDate", pToDate, DbType.DateTime, null, null);
return sp;
}
private StoredProcedure BaocaoSoghichep(DateTime? pFromDate, DateTime? pToDate)
{
SubSonic.StoredProcedure sp = new StoredProcedure("spSoGhiChep", DataService.GetInstance("ORM"), "dbo");
sp.Command.AddParameter("@pFromDate",pFromDate,DbType.DateTime,null,null);
sp.Command.AddParameter("@pToDate",pToDate,DbType.DateTime,null,null);
return sp;
}
public StoredProcedure aspnet_Membership_GetAllUsers(string ApplicationName,int PageIndex,int PageSize){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetAllUsers",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32);
sp.Command.AddParameter("PageSize",PageSize,DbType.Int32);
return sp;
}
/// <summary>
/// Execute Sql Query with Parameters
/// </summary>
/// <param name="sData">StoredProcedure Class</param>
/// <returns></returns>
public static DataSet SqlQueryDataSet(StoredProcedure sData)
{
DataSet sqlDataView = new DataSet();
try
{
SqlConnection conn = new SqlConnection(connect());
SqlCommand sqlcomm = new SqlCommand(sData.ProcName, conn);
sqlcomm.CommandType = CommandType.StoredProcedure;
int i = 0;
IEnumerator myEnumerator = sData.GetParams().GetEnumerator();
while (myEnumerator.MoveNext())
{
ParamData pData = (ParamData)myEnumerator.Current;
sqlcomm.Parameters.Add(pData.pName, pData.pDataType);
sqlcomm.Parameters[i].Value = pData.pValue;
i = i + 1;
}
SqlDataAdapter sqlAdapterView = new SqlDataAdapter(sqlcomm);
sqlAdapterView.Fill(sqlDataView);
return sqlDataView;
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex);
HttpContext.Current.Response.End();
return sqlDataView;
}
}
public void TestBasicParsing()
{
string proc1 = "CREATE PROC mysp @arg int=1 AS select 1\n";
var sp = new StoredProcedure(proc1);
WVPASSEQ(sp.name, "mysp");
var args = sp.args.ToArray();
WVPASSEQ(args[0].name, "arg");
WVPASSEQ(args[0].type, "int");
WVPASSEQ(args[0].defval, "1");
string proc2 = "CREATE PROC [mysp2]\n@arg1 int output ,\n" +
" @arg2 varchar(10) = 'asdf',@arg3 money AS ";
sp = new StoredProcedure(proc2);
WVPASSEQ(sp.name, "[mysp2]");
args = sp.args.ToArray();
WVPASSEQ(args[0].name, "arg1");
WVPASSEQ(args[0].type, "int");
WVPASSEQ(args[0].defval, "");
WVPASSEQ(args[1].name, "arg2");
WVPASSEQ(args[1].type, "varchar(10)");
WVPASSEQ(args[1].defval, "'asdf'");
WVPASSEQ(args[2].name, "arg3");
WVPASSEQ(args[2].type, "money");
WVPASSEQ(args[2].defval, "");
}
public static DataTable AllByParent(int? p_ParentCategoryID, bool p_ShowDisable)
{
StoredProcedure sp = new StoredProcedure("p_Categories_sl_ByParent");
sp["@ParentCategoryID"].Value = p_ParentCategoryID==null?DBNull.Value:(object)p_ParentCategoryID;
sp["@ShowDisable"].Value = p_ShowDisable;
return sp.ExecuteDataTable();
}
public StoredProcedure GetUser(string Email,string Password)
{
StoredProcedure sp=new StoredProcedure("GetUser",this.Provider);
sp.Command.AddParameter("Email",Email,DbType.AnsiString);
sp.Command.AddParameter("Password",Password,DbType.AnsiString);
return sp;
}
public static DataTable AllCategoriesDrugsByParent(int? p_ParentCategoryID, bool p_isShowAll)
{
StoredProcedure sp = new StoredProcedure("p_CategoriesDrugs_sl_ByParent");
sp["@CategoryID"].Value = p_ParentCategoryID == null ? DBNull.Value : (object)p_ParentCategoryID;
sp["@ShowAll"].Value = p_isShowAll;
return sp.ExecuteDataTable();
}
public static DataTable GetCategory(int p_CategoryID)
{
StoredProcedure sp = new StoredProcedure("p_Categories_s");
sp["@CategoryID"].Value = p_CategoryID;
return sp.ExecuteDataTable();
}
public static DataRow GetBanner(int p_BannerID)
{
StoredProcedure sp = new StoredProcedure("p_Banners_s");
sp["@BannerID"].Value = p_BannerID;
DataTable l_dtRes = sp.ExecuteDataTable();
if (l_dtRes.Rows.Count<=0) return null;
return l_dtRes.Rows[0];
}
public ProcessMessage(DbService iDbService, QueueService iQueueService)
{
_documentClient = iDbService.GetDocumentClient();
_client = iDbService.GetFirebaseClient();
_documentCollection = iDbService.GetDc("LMSCollection", "LMSRegistry");
_sp = iDbService.GetSp(_documentCollection, "Post");
_queue = iQueueService.GetQueue("queue");
}
public StoredProcedure aspnet_Membership_ChangePasswordQuestionAndAnswer(string ApplicationName,string UserName,string NewPasswordQuestion,string NewPasswordAnswer){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_ChangePasswordQuestionAndAnswer",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("UserName",UserName,DbType.String);
sp.Command.AddParameter("NewPasswordQuestion",NewPasswordQuestion,DbType.String);
sp.Command.AddParameter("NewPasswordAnswer",NewPasswordAnswer,DbType.String);
return sp;
}
public StoredProcedure aspnet_Membership_GetPasswordWithFormat(string ApplicationName,string UserName,bool UpdateLastLoginActivityDate,DateTime CurrentTimeUtc){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetPasswordWithFormat",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("UserName",UserName,DbType.String);
sp.Command.AddParameter("UpdateLastLoginActivityDate",UpdateLastLoginActivityDate,DbType.Boolean);
sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
return sp;
}
public StoredProcedure aspnet_Membership_GetUserByName(string ApplicationName,string UserName,DateTime CurrentTimeUtc,bool UpdateLastActivity){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetUserByName",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("UserName",UserName,DbType.String);
sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
sp.Command.AddParameter("UpdateLastActivity",UpdateLastActivity,DbType.Boolean);
return sp;
}
public StoredProcedure aspnet_Membership_FindUsersByName(string ApplicationName,string UserNameToMatch,int PageIndex,int PageSize){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_FindUsersByName",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("UserNameToMatch",UserNameToMatch,DbType.String);
sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32);
sp.Command.AddParameter("PageSize",PageSize,DbType.Int32);
return sp;
}
private StoredProcedure BaocaoDSBNtheoTestType(DateTime? pFromDate, DateTime? pToDate, string testType)
{
SubSonic.StoredProcedure sp = new StoredProcedure("spBaoCao_DSBN_TestType", DataService.GetInstance("ORM"),"dbo");
sp.Command.AddParameter("@pFromDate", pFromDate,DbType.DateTime,null,null);
sp.Command.AddParameter("@pToDate", pToDate,DbType.DateTime,null,null);
sp.Command.AddParameter("@testType",testType,DbType.String,null,null);
return sp;
}
public static StoredProcedure P_Branch_GetMaxBranchCode(int Depth,int ParentId)
{
StoredProcedure sp=new StoredProcedure("P_Branch_GetMaxBranchCode");
sp.Command.AddParameter("Depth",Depth,DbType.Int32);
sp.Command.AddParameter("ParentId",ParentId,DbType.Int32);
return sp;
}
public void SetParameter(IDataReader rdr, StoredProcedure.Parameter par)
{
par.SqlType = rdr[SqlSchemaVariable.DATA_TYPE].ToString();
par.DBType = GetDbType(par.SqlType);
string sMode = rdr[SqlSchemaVariable.MODE].ToString();
if (sMode == SqlSchemaVariable.MODE_INOUT)
par.Mode = ParameterDirection.InputOutput;
par.Name = rdr[SqlSchemaVariable.NAME].ToString();
}
public ScriptableObject(IScriptingSource scriptingSource, ScriptSchemaObjectBase schemaObject)
{
_scriptingSource = scriptingSource;
_schemaObject = schemaObject;
_table = _schemaObject as Table;
_view = _schemaObject as View;
_sproc = _schemaObject as StoredProcedure;
_udf = _schemaObject as UserDefinedFunction;
}
/// <summary>
/// Creates an object wrapper for the DeleteSeries Procedure
/// </summary>
public static StoredProcedure DeleteSeries(Guid? StudyStorageGUID, string SeriesInstanceUID)
{
var sp = new StoredProcedure("DeleteSeries", DataService.GetInstance("ORM"), "dbo");
sp.Command.AddParameter("@StudyStorageGUID", StudyStorageGUID, DbType.Guid, null, null);
sp.Command.AddParameter("@SeriesInstanceUID", SeriesInstanceUID, DbType.AnsiString, null, null);
return sp;
}
public static DataRow GetCategoryByCode(string p_CategoryCode)
{
StoredProcedure sp = new StoredProcedure("p_Categories_s_byCategoryCode");
sp["@CategoryCode"].Value = p_CategoryCode;
DataTable l_dtRes = sp.ExecuteDataTable();
if (l_dtRes.Rows.Count>0)
{
return l_dtRes.Rows[0];
}else {return null;}
}
public StoredProcedure SaveTransaction(long? TransactionID,string Description,decimal? Amount,DateTime? Date,string Tags)
{
StoredProcedure sp=new StoredProcedure("SaveTransaction",this.Provider);
sp.Command.AddParameter("TransactionID",TransactionID,DbType.Int64);
sp.Command.AddParameter("Description",Description,DbType.AnsiString);
sp.Command.AddParameter("Amount",Amount,DbType.Currency);
sp.Command.AddParameter("Date",Date,DbType.DateTime);
sp.Command.AddParameter("Tags",Tags,DbType.AnsiString);
return sp;
}
public StoredProcedure aspnet_Membership_GetPassword(string ApplicationName,string UserName,int MaxInvalidPasswordAttempts,int PasswordAttemptWindow,DateTime CurrentTimeUtc,string PasswordAnswer){
StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetPassword",this.Provider);
sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
sp.Command.AddParameter("UserName",UserName,DbType.String);
sp.Command.AddParameter("MaxInvalidPasswordAttempts",MaxInvalidPasswordAttempts,DbType.Int32);
sp.Command.AddParameter("PasswordAttemptWindow",PasswordAttemptWindow,DbType.Int32);
sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
sp.Command.AddParameter("PasswordAnswer",PasswordAnswer,DbType.String);
return sp;
}
internal static bool HasResult(this StoredProcedure storedProcedure)
{
return(storedProcedure.Output?.Any() ?? false);
}
private string GenerareScriptAsExecute(Server server, UrnCollection urns, ScriptingOptions options)
{
string script = string.Empty;
ScriptingObject scriptingObject = this.Parameters.ScriptingObjects[0];
Urn urn = urns[0];
// get the object
StoredProcedure sp = server.GetSmoObject(urn) as StoredProcedure;
Database parentObject = server.GetSmoObject(urn.Parent) as Database;
StringBuilder executeStatement = new StringBuilder();
// list of DECLARE <variable> <type>
StringBuilder declares = new StringBuilder();
// Parameters to be passed
StringBuilder parameterList = new StringBuilder();
if (sp == null || parentObject == null)
{
throw new InvalidOperationException(SR.ScriptingExecuteNotSupportedError);
}
WriteUseDatabase(parentObject, executeStatement, options);
// character string to put in front of each parameter. First one is just carriage return
// the rest will have a "," in front as well.
string newLine = Environment.NewLine;
string paramListPreChar = $"{newLine} ";
for (int i = 0; i < sp.Parameters.Count; i++)
{
StoredProcedureParameter spp = sp.Parameters[i];
declares.AppendFormat("DECLARE {0} {1}{2}"
, QuoteObjectName(spp.Name)
, GetDatatype(spp.DataType, options)
, newLine);
parameterList.AppendFormat("{0}{1}"
, paramListPreChar
, QuoteObjectName(spp.Name));
// if this is the first time through change the prefix to include a ","
if (i == 0)
{
paramListPreChar = $"{newLine} ,";
}
// mark any output parameters as such.
if (spp.IsOutputParameter)
{
parameterList.Append(" OUTPUT");
}
}
// build the execute statement
if (sp.ImplementationType == ImplementationType.TransactSql)
{
executeStatement.Append("EXECUTE @RC = ");
}
else
{
executeStatement.Append("EXECUTE ");
}
// get the object name
executeStatement.Append(GenerateSchemaQualifiedName(sp.Schema, sp.Name, options.SchemaQualify));
string formatString = sp.ImplementationType == ImplementationType.TransactSql
? "DECLARE @RC int{5}{0}{5}{1}{5}{5}{2} {3}{5}{4}"
: "{0}{5}{1}{5}{5}{2} {3}{5}{4}";
script = string.Format(CultureInfo.InvariantCulture, formatString,
declares,
SR.StoredProcedureScriptParameterComment,
executeStatement,
parameterList,
CommonConstants.DefaultBatchSeperator,
newLine);
return(script);
}
/// <summary>
///
/// </summary>
/// <param name="currentTbl">Data table that will be visualized in the grid</param>
/// <param name="spPram">list of kvp that hold the parameter checks</param>
/// <param name="spTxt">list of kvp that hold the text checks</param>
/// <param name="procedure">StoredProcedure Object</param>
/// <param name="logic">enum for what logic we need OR or AND</param>
private void AddProcToTable(DataTable currentTbl, List <KeyValuePair <string, bool> > spPram, List <KeyValuePair <string, bool> > spTxt
, StoredProcedure procedure, SearchLogic logic)
{
bool pramChecks = false;
bool spChecks = false;
bool currentCheck = false;
switch (logic)
{
case SearchLogic.AND:
foreach (KeyValuePair <string, bool> chk in spPram)
{
currentCheck = chk.Value;
if (!currentCheck)
{
pramChecks = false;
break;
}
else
{
pramChecks = true;
}
}
currentCheck = false;
spChecks = currentCheck;
foreach (KeyValuePair <string, bool> chk in spTxt)
{
currentCheck = chk.Value;
if (!currentCheck)
{
spChecks = false;
break;
}
else
{
spChecks = true;
}
}
if (spChecks && pramChecks)
{
DataRow row = currentTbl.NewRow();
string paramsstring = string.Join("\r\n", spPram).Replace(',', ' ').Replace('[', ' ').Replace(']', ' ');
var spparams = from StoredProcedureParameter prm in procedure.Parameters
select prm.Name + " " + prm.DataType.SqlDataType + " " + prm.DataType.MaximumLength + " " + prm.DefaultValue + "\r\n";
string paramsData = string.Join("\r\n", spparams);
row["ProcedureName"] = procedure.Name;
row["ProcedureChecks"] = paramsstring;
row["ProcedureParameters"] = paramsData;
row["ProcedureText"] = procedure.TextBody;
currentTbl.Rows.Add(row);
}
break;
case SearchLogic.OR:
foreach (KeyValuePair <string, bool> chk in spPram)
{
currentCheck = chk.Value;
if (!currentCheck)
{
pramChecks = false;
}
else
{
pramChecks = true;
break;
}
}
currentCheck = false;
spChecks = currentCheck;
foreach (KeyValuePair <string, bool> chk in spTxt)
{
currentCheck = chk.Value;
if (!currentCheck)
{
spChecks = false;
}
else
{
spChecks = true;
break;
}
}
if (spChecks || pramChecks)
{
DataRow row = currentTbl.NewRow();
string paramsstring = string.Join("\r\n", spPram).Replace(',', ' ').Replace('[', ' ').Replace(']', ' ');
var spparams = from StoredProcedureParameter prm in procedure.Parameters
select prm.Name + " " + prm.DataType.SqlDataType + " " + prm.DataType.MaximumLength + " " + prm.DefaultValue + "\r\n";
string paramsData = string.Join("\r\n", spparams);
row["ProcedureName"] = procedure.Name;
row["ProcedureChecks"] = paramsstring;
row["ProcedureParameters"] = paramsData;
row["ProcedureText"] = procedure.TextBody;
currentTbl.Rows.Add(row);
}
break;
}
}
public static DataSyncAction DropStoredProcedure(DataContext sourceDataContext, DataContext targetDataContext, StoredProcedure storedProcedure)
{
var builder = new StringBuilder();
builder.Append(StartThe)
.Append(storedProcedure.Description)
.Append(Space)
.Append(storedProcedure.Namespace)
.Append(EndDropped);
Func <string> script = () => ContextScriptFactory.DropStoredProcedure(sourceDataContext, targetDataContext, storedProcedure);
if (string.IsNullOrEmpty(script.Invoke()))
{
return(null);
}
return(new DataSyncAction(
storedProcedure
, storedProcedure.Namespace
, builder.ToString()
, DataSyncOperationType.DropStoredProcedure
, script
));
}
public ActionResult XacnhanPhieuTrathuocNhacungcap(TPhieuNhapxuatthuoc objPhieuNhap)
{
HisDuocProperties objHisDuocProperties = PropertyLib._HisDuocProperties;
string errorMessage = "";
try
{
using (var Scope = new TransactionScope())
{
using (var dbScope = new SharedDbConnectionScope())
{
SqlQuery sqlQuery = new Select().From(TPhieuNhapxuatthuocChitiet.Schema)
.Where(TPhieuNhapxuatthuocChitiet.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu);
TPhieuNhapxuatthuocChitietCollection objPhieuNhapCtCollection =
sqlQuery.ExecuteAsCollection <TPhieuNhapxuatthuocChitietCollection>();
foreach (TPhieuNhapxuatthuocChitiet objPhieuNhapCt in objPhieuNhapCtCollection)
{
//Insert dòng hủy vào TBiendongThuoc
TBiendongThuoc objXuatNhap = new TBiendongThuoc();
objXuatNhap.IdPhieu = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieu);
objXuatNhap.IdPhieuChitiet = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieuchitiet);
objXuatNhap.MaPhieu = Utility.sDbnull(objPhieuNhap.MaPhieu);
objXuatNhap.DonGia = Utility.DecimaltoDbnull(objPhieuNhapCt.DonGia);
objXuatNhap.NgayHoadon = objPhieuNhap.NgayHoadon;
objXuatNhap.GiaBan = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaBan);
objXuatNhap.GiaNhap = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaNhap);
objXuatNhap.SoDky = objPhieuNhapCt.SoDky;
objXuatNhap.SoQdinhthau = objPhieuNhapCt.SoQdinhthau;
objXuatNhap.SoLo = objPhieuNhapCt.SoLo;
objXuatNhap.IdThuockho = Utility.Int32Dbnull(objPhieuNhapCt.IdThuockho);
objXuatNhap.KieuThuocvattu = Utility.sDbnull(objPhieuNhapCt.KieuThuocvattu);
objXuatNhap.SoChungtuKemtheo = "";
objXuatNhap.Noitru = 0;
objXuatNhap.QuayThuoc = 0;
objXuatNhap.GiaBhyt = 0;
objXuatNhap.GiaBhytCu = 0;
objXuatNhap.GiaPhuthuDungtuyen = 0;
objXuatNhap.GiaPhuthuTraituyen = 0;
objXuatNhap.DuTru = 0;
objXuatNhap.SoHoadon = Utility.sDbnull(objPhieuNhap.SoHoadon);
objXuatNhap.PhuThu = 0;
objXuatNhap.SoLuong = Utility.Int32Dbnull(objPhieuNhapCt.SoLuong);
objXuatNhap.NgayTao = globalVariables.SysDate;
objXuatNhap.NguoiTao = globalVariables.UserName;
objXuatNhap.ThanhTien = Utility.DecimaltoDbnull(objPhieuNhapCt.ThanhTien);
objXuatNhap.IdThuoc = Utility.Int32Dbnull(objPhieuNhapCt.IdThuoc);
objXuatNhap.Vat = Utility.Int32Dbnull(objPhieuNhap.Vat);
objXuatNhap.IdNhanvien = Utility.Int16Dbnull(objPhieuNhap.IdNhanvien);
objXuatNhap.IdKho = Utility.Int16Dbnull(objPhieuNhap.IdKhoxuat);
objXuatNhap.NgayHethan = objPhieuNhapCt.NgayHethan.Date;
objXuatNhap.MaNhacungcap = objPhieuNhap.MaNhacungcap;
objXuatNhap.MaLoaiphieu = objPhieuNhap.LoaiPhieu;
objXuatNhap.TenLoaiphieu = objPhieuNhap.TenLoaiphieu;
objXuatNhap.NgayBiendong = objPhieuNhap.NgayHoadon;
objXuatNhap.IsNew = true;
objXuatNhap.Save();
StoredProcedure sp = SPs.ThuocXuatkho(objPhieuNhap.IdKhoxuat, objPhieuNhapCt.IdThuoc,
objPhieuNhapCt.NgayHethan, objPhieuNhapCt.GiaNhap, objPhieuNhapCt.GiaBan,
Utility.DecimaltoDbnull(objPhieuNhapCt.Vat),
Utility.Int32Dbnull(objXuatNhap.SoLuong), objPhieuNhapCt.IdChuyen, objPhieuNhapCt.MaNhacungcap, objPhieuNhapCt.SoLo, objHisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errorMessage);
sp.Execute();
}
new Update(TPhieuNhapxuatthuoc.Schema)
.Set(TPhieuNhapxuatthuoc.Columns.IdNhanvien).EqualTo(globalVariables.gv_intIDNhanvien)
.Set(TPhieuNhapxuatthuoc.Columns.NguoiXacnhan).EqualTo(globalVariables.UserName)
.Set(TPhieuNhapxuatthuoc.Columns.NgayXacnhan).EqualTo(globalVariables.SysDate)
.Set(TPhieuNhapxuatthuoc.Columns.TrangThai).EqualTo(1)
.Where(TPhieuNhapxuatthuoc.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu).Execute();
}
Scope.Complete();
return(ActionResult.Success);
}
}
catch (Exception exception)
{
log.Error("Loi ban ra tu sp :{0}", errorMessage);
log.Error("Loi trong qua trinh xac nhan don thuoc :{0}", exception);
return(ActionResult.Error);
}
}
public StoredProcedureConfiguration GetStoredProcedureConfiguration(StoredProcedure storedProcedure)
{
return(this.StoredProcedures.FirstOrDefault(x => x.Name == storedProcedure.Name));
}
public ActionResult XacNhanPhieuHuy_thanhly_thuoc(TPhieuNhapxuatthuoc objPhieuNhap, DateTime ngayxacnhan, ref string errMsg)
{
HisDuocProperties objHisDuocProperties = PropertyLib._HisDuocProperties;
string errorMessage = "";
try
{
using (var Scope = new TransactionScope())
{
using (var dbScope = new SharedDbConnectionScope())
{
SqlQuery sqlQuery = new Select().From(TPhieuNhapxuatthuocChitiet.Schema)
.Where(TPhieuNhapxuatthuocChitiet.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu);
TPhieuNhapxuatthuocChitietCollection objPhieuNhapCtCollection =
sqlQuery.ExecuteAsCollection <TPhieuNhapxuatthuocChitietCollection>();
objPhieuNhap.NgayXacnhan = ngayxacnhan;
foreach (TPhieuNhapxuatthuocChitiet objPhieuNhapCt in objPhieuNhapCtCollection)
{
//Kiểm tra đề phòng Kho A-->Xuất kho B. Kho B xác nhận-->Xuất kho C. Kho B hủy xác nhận. Kho C xác nhận dẫn tới việc kho B chưa có thuốc để trừ kho
ActionResult _Kiemtrathuocxacnhan = Kiemtrathuocxacnhan(objPhieuNhap, objPhieuNhapCt, ref errMsg);
if (_Kiemtrathuocxacnhan != ActionResult.Success)
{
return(_Kiemtrathuocxacnhan);
}
long idthuockho = -1;
StoredProcedure sp = SPs.ThuocXuatkho(objPhieuNhap.IdKhoxuat, objPhieuNhapCt.IdThuoc,
objPhieuNhapCt.NgayHethan, objPhieuNhapCt.GiaNhap, objPhieuNhapCt.GiaBan,
Utility.DecimaltoDbnull(objPhieuNhapCt.Vat),
Utility.Int32Dbnull(objPhieuNhapCt.SoLuong), objPhieuNhapCt.IdChuyen,
objPhieuNhapCt.MaNhacungcap, objPhieuNhapCt.SoLo,
objHisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errorMessage);
sp.Execute();
//Insert dòng kho xuất
TBiendongThuoc objXuatNhap = new TBiendongThuoc();
objXuatNhap.IdPhieu = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieu);
objXuatNhap.IdPhieuChitiet = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieuchitiet);
objXuatNhap.MaPhieu = Utility.sDbnull(objPhieuNhap.MaPhieu);
objXuatNhap.DonGia = Utility.DecimaltoDbnull(objPhieuNhapCt.DonGia);
objXuatNhap.GiaBan = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaBan);
objXuatNhap.GiaNhap = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaNhap);
objXuatNhap.SoHoadon = Utility.sDbnull(objPhieuNhap.SoHoadon);
objXuatNhap.GiaBhyt = objPhieuNhapCt.GiaBhyt;
objXuatNhap.GiaBhytCu = objPhieuNhapCt.GiaBhytCu;
objXuatNhap.GiaPhuthuDungtuyen = objPhieuNhapCt.GiaPhuthuDungtuyen;
objXuatNhap.GiaPhuthuTraituyen = objPhieuNhapCt.GiaPhuthuTraituyen;
objXuatNhap.NgayNhap = objPhieuNhapCt.NgayNhap;
objXuatNhap.Noitru = 0;
objXuatNhap.QuayThuoc = 0;
objXuatNhap.PhuThu = 0;
objXuatNhap.DuTru = objPhieuNhap.DuTru;
objXuatNhap.SoLuong = Utility.Int32Dbnull(objPhieuNhapCt.SoLuong);
objXuatNhap.NgayTao = globalVariables.SysDate;
objXuatNhap.NguoiTao = globalVariables.UserName;
objXuatNhap.IdThuockho = Utility.Int32Dbnull(objPhieuNhapCt.IdThuockho);
objXuatNhap.IdChuyen = Utility.Int32Dbnull(objPhieuNhapCt.IdChuyen);
objXuatNhap.ThanhTien = Utility.DecimaltoDbnull(objPhieuNhapCt.ThanhTien);
objXuatNhap.IdThuoc = Utility.Int32Dbnull(objPhieuNhapCt.IdThuoc);
objXuatNhap.Vat = Utility.Int32Dbnull(objPhieuNhap.Vat);
objXuatNhap.IdNhanvien = Utility.Int16Dbnull(objPhieuNhap.IdNhanvien);
objXuatNhap.IdKho = Utility.Int16Dbnull(objPhieuNhap.IdKhoxuat);
objXuatNhap.NgayHethan = objPhieuNhapCt.NgayHethan.Date;
objXuatNhap.MaNhacungcap = objPhieuNhapCt.MaNhacungcap;
objXuatNhap.SoChungtuKemtheo = objPhieuNhap.SoChungtuKemtheo;
objXuatNhap.SoDky = objPhieuNhapCt.SoDky;
objXuatNhap.SoQdinhthau = objPhieuNhapCt.SoQdinhthau;
objXuatNhap.SoLo = objPhieuNhapCt.SoLo;
objXuatNhap.MaLoaiphieu = objPhieuNhap.LoaiPhieu;
objXuatNhap.TenLoaiphieu = objPhieuNhap.TenLoaiphieu;
objXuatNhap.NgayBiendong = objPhieuNhap.NgayXacnhan;
objXuatNhap.NgayHoadon = objPhieuNhap.NgayHoadon;
objXuatNhap.KieuThuocvattu = objPhieuNhapCt.KieuThuocvattu;
objXuatNhap.IsNew = true;
objXuatNhap.Save();
}
new Update(TPhieuNhapxuatthuoc.Schema)
.Set(TPhieuNhapxuatthuoc.Columns.NguoiSua).EqualTo(globalVariables.UserName)
.Set(TPhieuNhapxuatthuoc.Columns.NgaySua).EqualTo(globalVariables.SysDate)
.Set(TPhieuNhapxuatthuoc.Columns.NguoiXacnhan).EqualTo(globalVariables.UserName)
.Set(TPhieuNhapxuatthuoc.Columns.NgayXacnhan).EqualTo(ngayxacnhan)
.Set(TPhieuNhapxuatthuoc.Columns.TrangThai).EqualTo(1)
.Where(TPhieuNhapxuatthuoc.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu)
.And(TPhieuNhapxuatthuoc.LoaiPhieuColumn).IsEqualTo(objPhieuNhap.LoaiPhieu).Execute();
}
Scope.Complete();
return(ActionResult.Success);
}
}
catch (Exception ex)
{
Utility.CatchException("Lỗi khi xác nhận phiếu ", ex);
return(ActionResult.Error);
}
}
/// <summary>
/// Replace the specified stored procedure.
/// </summary>
/// <param name="client">document client.</param>
/// <param name="storedProcedureUri">the self-link for the attachment.</param>
/// <param name="storedProcedure">the updated stored procedure.</param>
/// <param name="options">the request options for the request.</param>
/// <returns>The task object representing the service response for the asynchronous operation.</returns>
public static Task <ResourceResponse <StoredProcedure> > ReplaceStoredProcedureExAsync(this DocumentClient client, StoredProcedure storedProcedure, RequestOptions options = null)
{
SwapLinkIfNeeded(client, storedProcedure);
return(client.ReplaceStoredProcedureAsync(storedProcedure, options));
}
internal static IEnumerable <StoredProcedureInputModel> GetOutputs(this StoredProcedure storedProcedure)
{
return(storedProcedure.Input?.Where(i => i.IsOutput));
}
public static string GenerateStoredProcedureCode(StoredProcedure pProc, string columnPrefix, bool pIncludeSchema)
{
string newLine = Environment.NewLine;
string str = "\t";
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("using System;").Append(newLine);
stringBuilder.Append("using System.Data;");
stringBuilder.Append("using System.Data.SqlClient;");
stringBuilder.Append("namespace Tables.").Append(pProc.Name).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append("public sealed class Proc : ").Append(typeof(StoredProcBase).ToString()).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public static readonly Proc Instance = new Proc();").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Proc() : base(DATABASE, \"").Append(!pIncludeSchema || string.IsNullOrEmpty(pProc.Schema) ? string.Empty : pProc.Schema + ".").Append(pProc.Name).Append("\", typeof(Row)) {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("//AddColumns(");
stringBuilder.Append(");").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Sql.IResult Execute(");
int index1 = 0;
while (index1 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index1];
if (index1 > 0)
{
stringBuilder.Append(", ");
}
if (spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue)
{
stringBuilder.Append("out ");
}
stringBuilder.Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(" ").Append(spParameter.Name);
checked { ++index1; }
}
if (pProc.Parameters.Count > 0)
{
stringBuilder.Append(", ");
}
stringBuilder.Append("Sql.Transaction transaction){").Append(newLine).Append(newLine);
int index2 = 0;
while (index2 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index2];
stringBuilder.Append(str).Append(str).Append(str).Append("SqlParameter p").Append(index2.ToString()).Append(" = new SqlParameter(\"").Append(spParameter.Name).Append("\", SqlDbType.").Append(DbColumnFactory.GetSqlType(spParameter.ParamType).ToString()).Append(");").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Direction = ParameterDirection.").Append(spParameter.Direction.ToString()).Append(";").Append(newLine);
if (spParameter.Direction == ParameterDirection.Input || spParameter.Direction == ParameterDirection.InputOutput)
{
stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Value = ").Append(spParameter.Name).Append(";").Append(newLine);
}
stringBuilder.Append(newLine);
checked { ++index2; }
}
stringBuilder.Append(str).Append(str).Append(str).Append("Sql.IResult result = ExecuteProcedure(transaction");
int num = 0;
while (num < pProc.Parameters.Count)
{
stringBuilder.Append(", p").Append(num.ToString());
checked { ++num; }
}
stringBuilder.Append(");").Append(newLine).Append(newLine);
int index3 = 0;
while (index3 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index3];
if (spParameter.Direction == ParameterDirection.InputOutput || spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue)
{
stringBuilder.Append(str).Append(str).Append(str).Append(spParameter.Name).Append(" = (").Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(")").Append("p").Append(index3.ToString()).Append(".Value;").Append(newLine);
}
checked { ++index3; }
}
stringBuilder.Append(str).Append(str).Append(str).Append("return result;").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine).Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Row this[int pIndex, Sql.IResult pResult]{").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Row)pResult.GetRow(this, pIndex); }").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append("public sealed class Row : ").Append(typeof(Record).ToString()).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("private new Proc Tbl {").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Proc)base.Tbl; }").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Row() : base(Proc.Instance) {").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(str).Append("}").Append(newLine);
stringBuilder.Append("}");
return(stringBuilder.ToString());
}
public abstract string DropStoredProcedure(DataContext sourceDataContext, DataContext targetDataContext, StoredProcedure storedProcedure);
public static string OutPut_ID(StoredProcedure sp)
{
return(sp.OutputValues[0].ToString());
}
/// <summary>
/// Lista de métodos a generar.
/// </summary>
/// <date>2007-5-25T00:00:00</date>
/// <author>Marcelo Oviedo</author>
//public List<MethodInfo> Methods
//{
// get { return _Methods; }
// set { _Methods = value; }
//}
#endregion
#region [Constructors]
/// <summary>
/// Constructor por defecto.
/// </summary>
/// <author>Marcelo Oviedo</author>
//public EntityInfo(Table pTable)
//{
// _Table = pTable;
// _Methods = new List<MethodInfo>();
//}
/// <summary>
///
/// </summary>
/// <param name="pStoredProcedure"></param>
/// <author>Marcelo Oviedo</author>
public EntityInfo(StoredProcedure pStoredProcedure)
{
_StoredProcedure = pStoredProcedure;
//_Methods = new List<MethodInfo>();
}
internal static async Task PartitionedCollectionSmokeTest(DocumentClient client, bool sharedOffer = false, bool sharedThroughputCollections = false, int numberOfCollections = 1)
{
if (!sharedOffer && sharedThroughputCollections)
{
throw new ArgumentException("Shared throughput collections are not supported without shared offer");
}
string uniqDatabaseName = string.Format("SmokeTest_{0}", Guid.NewGuid().ToString("N"));
RequestOptions options = new RequestOptions {
OfferThroughput = 50000
};
Database database = sharedOffer ? await client.CreateDatabaseAsync(new Database { Id = uniqDatabaseName }, options) : await client.CreateDatabaseAsync(new Database { Id = uniqDatabaseName });
Assert.AreEqual(database.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName));
Database readbackdatabase = await client.ReadDatabaseAsync(database.SelfLink);
List <dynamic> results = await ClientTestsUtils.SqlQueryDatabases(client, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDatabaseName), 10);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(database.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.IsTrue((await ClientTestsUtils.ReadFeedDatabases(client)).Any((db) => db.Id == uniqDatabaseName));
results = await ClientTestsUtils.SqlQueryDatabases(client, string.Format(@"select r._rid, r.id from root r where r.id = ""{0}""", uniqDatabaseName), 10);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 database");
Assert.AreEqual(database.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(database.ResourceId, (await client.ReadDatabaseAsync(database.SelfLink)).Resource.ResourceId);
Assert.AreEqual(((Database)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName));
ArrayList testCollections = new ArrayList();
for (int i = 0; i < numberOfCollections; i++)
{
string uniqCollectionName = "SmokeTestCollection" + Guid.NewGuid().ToString("N");
PartitionKeyDefinition partitionKeyDefinition = new PartitionKeyDefinition
{
Paths = new System.Collections.ObjectModel.Collection <string> {
"/id"
},
Kind = PartitionKind.Hash
};
DocumentCollection collection;
if (sharedThroughputCollections)
{
collection = await TestCommon.CreateCollectionAsync(client, database.SelfLink, new DocumentCollection { Id = uniqCollectionName, PartitionKey = partitionKeyDefinition });
}
else
{
collection = await TestCommon.CreateCollectionAsync(client, database.SelfLink, new DocumentCollection { Id = uniqCollectionName, PartitionKey = partitionKeyDefinition }, options);
}
Assert.AreEqual(collection.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, typeof(DocumentCollection)));
results = await SqlQueryCollections(client, database.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqCollectionName), 10); // query through database link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 collection");
Assert.AreEqual(collection.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryCollections(client, database.CollectionsLink, string.Format(@"select r._rid, r.id from root r where r.id = ""{0}""", uniqCollectionName), 10); // query through CollectionsLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 collection");
Assert.AreEqual(collection.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(1, (await ReadFeedCollections(client, database.SelfLink)).Count(item => item.Id == uniqCollectionName)); // read through database link
Assert.AreEqual(1, (await ReadFeedCollections(client, database.CollectionsLink)).Count(item => item.Id == uniqCollectionName)); // read through CollectionsLink
Assert.AreEqual(collection.ResourceId, (await client.ReadDocumentCollectionAsync(collection.SelfLink)).Resource.ResourceId);
Assert.AreEqual(((DocumentCollection)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, typeof(DocumentCollection)));
testCollections.Add(collection);
string uniqDocumentName = "SmokeTestDocument" + Guid.NewGuid().ToString("N");
LinqGeneralBaselineTests.Book myDocument = new LinqGeneralBaselineTests.Book();
myDocument.Id = uniqDocumentName;
myDocument.Title = "My Book"; //Simple Property.
myDocument.Languages = new LinqGeneralBaselineTests.Language[] { new LinqGeneralBaselineTests.Language {
Name = "English", Copyright = "London Publication"
}, new LinqGeneralBaselineTests.Language {
Name = "French", Copyright = "Paris Publication"
} }; //Array Property
myDocument.Author = new LinqGeneralBaselineTests.Author {
Name = "Don", Location = "France"
}; //Complex Property
myDocument.Price = 9.99;
myDocument.Editions = new List <LinqGeneralBaselineTests.Edition>()
{
new LinqGeneralBaselineTests.Edition()
{
Name = "First", Year = 2001
}, new LinqGeneralBaselineTests.Edition()
{
Name = "Second", Year = 2005
}
};
Document document = await client.CreateDocumentAsync(collection.SelfLink, myDocument);
Assert.AreEqual(document.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, uniqDocumentName, typeof(Document)));
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryDocuments(client, collection.DocumentsLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10); // query through DocumentsLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(1, (await ReadFeedDocuments(client, collection.SelfLink)).Count(item => item.Id == uniqDocumentName)); // read through collection link
Assert.AreEqual(1, (await ReadFeedDocuments(client, collection.DocumentsLink)).Count(item => item.Id == uniqDocumentName)); // read through DocumentsLink
if (client.QueryCompatibilityMode != QueryCompatibilityMode.SqlQuery)
{
//Test query with parameters
results = await SqlQueryDocuments(client, collection.SelfLink,
new SqlQuerySpec
{
QueryText = @"select r._rid from root r where r.id = @id",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@id", uniqDocumentName)
}
}, 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId);
}
RequestOptions docReplaceRequestOptions = new RequestOptions {
PartitionKey = new PartitionKey(document.Id)
};
FeedOptions docReplaceFeedOptions = new FeedOptions {
EnableCrossPartitionQuery = true, PartitionKey = new PartitionKey(document.Id)
};
myDocument.Title = "My_Book_v2";
document = await client.ReplaceDocumentAsync(document.AltLink, myDocument);
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10, docReplaceFeedOptions);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqDocumentName), 10); // query through collection
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
results = await SqlQueryDocuments(client, collection.DocumentsLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqDocumentName), 10); // query through DocumentsLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
Assert.AreEqual(((Document)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, uniqDocumentName, typeof(Document)));
// No Range Index on ts - override with scan
FeedOptions queryFeedOptions1 = new FeedOptions()
{
EnableScanInQuery = true, EnableCrossPartitionQuery = true
};
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price>0"), 10, queryFeedOptions1);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
FeedOptions queryFeedOptions2 = new FeedOptions()
{
EmitVerboseTracesInQuery = true, EnableCrossPartitionQuery = true
};
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price=9.99"), 10, queryFeedOptions2);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
FeedOptions queryFeedOptions3 = new FeedOptions()
{
EmitVerboseTracesInQuery = false, EnableCrossPartitionQuery = true
};
results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price=9.99"), 10, queryFeedOptions3);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 document");
string uniqStoredProcedureName = "SmokeTestStoredProcedure" + Guid.NewGuid().ToString();
StoredProcedure storedProcedure = await client.CreateStoredProcedureAsync(collection.SelfLink, new StoredProcedure { Id = uniqStoredProcedureName, Body = "function f() {var x = 10;}" });
results = await SqlQueryStoredProcedures(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id = ""{0}""", uniqStoredProcedureName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure");
Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryStoredProcedures(client, collection.StoredProceduresLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id = ""{0}""", uniqStoredProcedureName), 10); // query through StoredProceduresLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure");
Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(1, (await ReadFeedStoredProcedures(client, collection.SelfLink)).Count(item => item.Id == uniqStoredProcedureName)); // read through collection link
Assert.AreEqual(1, (await ReadFeedStoredProcedures(client, collection.StoredProceduresLink)).Count(item => item.Id == uniqStoredProcedureName)); // read through StoredProceduresLink
storedProcedure.Body = "function f() {var x= 20;}";
storedProcedure = await client.ReplaceStoredProcedureAsync(storedProcedure);
results = await SqlQueryStoredProcedures(client, collection.StoredProceduresLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", storedProcedure.Id), 10); // query through StoredProceduresLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure");
Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(storedProcedure.ResourceId, (await client.ReadStoredProcedureAsync(storedProcedure.SelfLink)).Resource.ResourceId);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure");
string uniqTriggerName = "SmokeTestTrigger" + Guid.NewGuid().ToString("N");
Trigger trigger = await client.CreateTriggerAsync(collection.SelfLink, new Trigger { Id = uniqTriggerName, Body = "function f() {var x = 10;}", TriggerOperation = TriggerOperation.All, TriggerType = TriggerType.Pre });
results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger");
Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryTriggers(client, collection.TriggersLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10); // query through TriggersLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger");
Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(1, (await ReadFeedTriggers(client, collection.SelfLink)).Count(item => item.Id == uniqTriggerName)); // read through collection link
Assert.AreEqual(1, (await ReadFeedTriggers(client, collection.TriggersLink)).Count(item => item.Id == uniqTriggerName)); // read through TriggersLink
trigger.Body = "function f() {var x = 10;}";
trigger = await client.ReplaceTriggerAsync(trigger);
results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger");
Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(trigger.ResourceId, (await client.ReadTriggerAsync(trigger.SelfLink)).Resource.ResourceId);
results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqTriggerName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger");
results = await SqlQueryTriggers(client, collection.TriggersLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqTriggerName), 10); // query through TriggersLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger");
string uniqUserDefinedFunctionName = "SmokeTestUserDefinedFunction" + Guid.NewGuid().ToString("N");
UserDefinedFunction userDefinedFunction = await client.CreateUserDefinedFunctionAsync(collection.SelfLink, new UserDefinedFunction { Id = uniqUserDefinedFunctionName, Body = "function (){ var x = 10;}" });
results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction");
Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId);
results = await SqlQueryUserDefinedFunctions(client, collection.UserDefinedFunctionsLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10); // query through UserDefinedFunctionsLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction");
Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(1, (await ReadFeedUserDefinedFunctions(client, collection.SelfLink)).Count(item => item.Id == uniqUserDefinedFunctionName)); // read through collection link
Assert.AreEqual(1, (await ReadFeedUserDefinedFunctions(client, collection.UserDefinedFunctionsLink)).Count(item => item.Id == uniqUserDefinedFunctionName)); // read through UserDefinedFunctionsLink
userDefinedFunction.Body = "function (){ var x = 10;}";
userDefinedFunction = await client.ReplaceUserDefinedFunctionAsync(userDefinedFunction);
results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10);
Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction");
Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId);
Assert.AreEqual(userDefinedFunction.ResourceId, (await client.ReadUserDefinedFunctionAsync(userDefinedFunction.SelfLink)).Resource.ResourceId);
results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqUserDefinedFunctionName), 10); // query through collection link
Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction");
results = await SqlQueryUserDefinedFunctions(client, collection.UserDefinedFunctionsLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqUserDefinedFunctionName), 10); // query through UserDefinedFunctionsLink
Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction");
//Test select array
IDocumentQuery <dynamic> queryArray = client.CreateDocumentQuery(collection.SelfLink, "SELECT VALUE [1, 2, 3, 4]").AsDocumentQuery();
JArray result = queryArray.ExecuteNextAsync().Result.FirstOrDefault();
Assert.AreEqual(result[0], 1);
Assert.AreEqual(result[1], 2);
Assert.AreEqual(result[2], 3);
Assert.AreEqual(result[3], 4);
RequestOptions requestOptions = new RequestOptions {
PartitionKey = new PartitionKey(document.Id)
};
await client.DeleteDocumentAsync(document.SelfLink, requestOptions);
}
foreach (DocumentCollection collection in testCollections)
{
await client.DeleteDocumentCollectionAsync(collection.SelfLink);
}
await client.DeleteDatabaseAsync(database.SelfLink);
}
protected override void SetOutputParameterValue(DbCommand command)
{
var cm = command;
DbParameter p = null;
p = cm.Parameters[0] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.CharColumn = (String)p.Value;
}
p = cm.Parameters[1] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.NCharColumn = (String)p.Value;
}
p = cm.Parameters[2] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.VarCharColumn = (String)p.Value;
}
p = cm.Parameters[3] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.NVarCharColumn = (String)p.Value;
}
p = cm.Parameters[4] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.BitColumn = ((UInt64)p.Value != 0);
}
p = cm.Parameters[5] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TinyIntColumn = (SByte)p.Value;
}
p = cm.Parameters[6] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.SmallIntColumn = (Int16)p.Value;
}
p = cm.Parameters[7] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.MediumIntColumn = (Int32)p.Value;
}
p = cm.Parameters[8] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.IntColumn = (Int32)p.Value;
}
p = cm.Parameters[9] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.BigIntColumn = (Int64)p.Value;
}
p = cm.Parameters[10] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TinyIntUnsignedColumn = (Byte)p.Value;
}
p = cm.Parameters[11] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.SmallIntUnsignedColumn = (UInt16)p.Value;
}
p = cm.Parameters[12] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.MediumIntUnsignedColumn = (UInt32)p.Value;
}
p = cm.Parameters[13] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.IntUnsignedColumn = (UInt32)p.Value;
}
p = cm.Parameters[14] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.BigIntUnsignedColumn = (UInt64)p.Value;
}
p = cm.Parameters[15] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.FloatColumn = (Single)p.Value;
}
p = cm.Parameters[16] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.DoubleColumn = (Double)p.Value;
}
p = cm.Parameters[17] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.DecimalColumn = (Decimal)p.Value;
}
p = cm.Parameters[18] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.NumericColumn = (Decimal)p.Value;
}
p = cm.Parameters[19] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.DateColumn = (DateTime)p.Value;
}
p = cm.Parameters[20] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.DateTimeColumn = (DateTime)p.Value;
}
p = cm.Parameters[21] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TimeColumn = (TimeSpan)p.Value;
}
p = cm.Parameters[22] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.YearColumn = (Int32)p.Value;
}
p = cm.Parameters[23] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.BinaryColumn = (Byte[])p.Value;
}
p = cm.Parameters[24] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.VarBinaryColumn = (Byte[])p.Value;
}
p = cm.Parameters[25] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TinyBlobColumn = (Byte[])p.Value;
}
p = cm.Parameters[26] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.MediumBlobColumn = (Byte[])p.Value;
}
p = cm.Parameters[27] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.BlobColumn = (Byte[])p.Value;
}
p = cm.Parameters[28] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.LongBlobColumn = (Byte[])p.Value;
}
p = cm.Parameters[29] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TinyTextColumn = (String)p.Value;
}
p = cm.Parameters[30] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TextColumn = (String)p.Value;
}
p = cm.Parameters[31] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.MediumTextColumn = (String)p.Value;
}
p = cm.Parameters[32] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.LongTextColumn = (String)p.Value;
}
p = cm.Parameters[33] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.TimestampColumn = (DateTime)p.Value;
}
p = cm.Parameters[34] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.EnumColumn = StoredProcedure.ToEnum <MyEnum>(p.Value as String) ?? this.EnumColumn;
}
p = cm.Parameters[35] as DbParameter;
if (p.Value != DBNull.Value && p.Value != null)
{
this.SetColumn = StoredProcedure.ToEnum <MySet>(p.Value as String) ?? this.SetColumn;
}
}
public void TestStoredProcedure()
{
// Create a document client with a customer json serializer settings
JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
serializerSettings.Converters.Add(new ObjectStringJsonConverter <SerializedObject>(_ => _.Name, _ => SerializedObject.Parse(_)));
ConnectionPolicy connectionPolicy = new ConnectionPolicy {
ConnectionMode = ConnectionMode.Gateway
};
ConsistencyLevel defaultConsistencyLevel = ConsistencyLevel.Session;
DocumentClient client = CreateDocumentClient(
this.hostUri,
this.masterKey,
serializerSettings,
connectionPolicy,
defaultConsistencyLevel);
// Create a simple stored procedure
var scriptId = "bulkImportScript";
var sproc = new StoredProcedure
{
Id = scriptId,
Body = @"
function bulkImport(docs) {
var collection = getContext().getCollection();
var collectionLink = collection.getSelfLink();
// The count of imported docs, also used as current doc index.
var count = 0;
// Validate input.
if (!docs) throw new Error(""The array is undefined or null."");
var docsLength = docs.length;
if (docsLength == 0)
{
getContext().getResponse().setBody(0);
}
// Call the CRUD API to create a document.
tryCreate(docs[count], callback);
// Note that there are 2 exit conditions:
// 1) The createDocument request was not accepted.
// In this case the callback will not be called, we just call setBody and we are done.
// 2) The callback was called docs.length times.
// In this case all documents were created and we don't need to call tryCreate anymore. Just call setBody and we are done.
function tryCreate(doc, callback) {
// If you are sure that every document will contain its own (unique) id field then
// disable the option to auto generate ids.
// by leaving this on, the entire document is parsed to check if there is an id field or not
// by disabling this, parsing of the document is skipped because you're telling DocumentDB
// that you are providing your own ids.
// depending on the size of your documents making this change can have a significant
// improvement on document creation.
var options = {
disableAutomaticIdGeneration: true
};
var isAccepted = collection.createDocument(collectionLink, doc, options, callback);
// If the request was accepted, callback will be called.
// Otherwise report current count back to the client,
// which will call the script again with remaining set of docs.
// This condition will happen when this stored procedure has been running too long
// and is about to get cancelled by the server. This will allow the calling client
// to resume this batch from the point we got to before isAccepted was set to false
if (!isAccepted) getContext().getResponse().setBody(count);
}
// This is called when collection.createDocument is done and the document has been persisted.
function callback(err, doc, options)
{
if (err) throw err;
// One more document has been inserted, increment the count.
count++;
if (count >= docsLength)
{
// If we have created all documents, we are done. Just set the response.
getContext().getResponse().setBody(count);
}
else
{
// Create next document.
tryCreate(docs[count], callback);
}
}
}
"
};
sproc = client.CreateStoredProcedureAsync(collectionUri, sproc).Result.Resource;
var doc = new MyObject(1);
var args = new dynamic[] { new dynamic[] { doc } };
RequestOptions requestOptions = ApplyRequestOptions(new RequestOptions {
PartitionKey = new PartitionKey("value")
}, serializerSettings);
StoredProcedureResponse <int> scriptResult = client.ExecuteStoredProcedureAsync <int>(
sproc.SelfLink,
requestOptions,
args).Result;
var docUri = UriFactory.CreateDocumentUri(databaseName, collectionName, doc.id);
var readDoc = client.ReadDocumentAsync <MyObject>(docUri, requestOptions).Result.Document;
Assert.IsNotNull(readDoc.SerializedObject);
Assert.AreEqual(doc.SerializedObject.Name, readDoc.SerializedObject.Name);
}
/// <summary>
/// get the Proc data table for matching procs
/// </summary>
/// <param name="pramName">param name</param>
/// <param name="paramType">param sql datatype</param>
/// <param name="isOutput">Bool is the param output</param>
/// <param name="isNulable">Bool is the param nullable</param>
/// <param name="isReadOnly">Bool is the param readonly</param>
/// <param name="columnName">Column name text to serarch</param>
/// <param name="tableName">Table name text to serarch</param>
/// <param name="freeText">free text to serarch</param>
/// <param name="sqlFunction">text to serarch for sql func</param>
/// <param name="searchLogic">enum search locgic And or OR logic</param>
/// <returns></returns>
private DataTable GetProcsByParamerterFilter(
string pramName, SqlDataType paramType, bool isOutput, bool isNulable, bool isReadOnly
, string columnName, string tableName, string freeText, string sqlFunction, SearchLogic searchLogic
)
{
procsTable.Clear();
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.Authentication = SqlAuthenticationMethod.SqlPassword;
connStr.ConnectTimeout = 0;
connStr.UserID = ConfigurationManager.AppSettings["ServerUser"].ToString();
connStr.Password = ConfigurationManager.AppSettings["ServerUserPassword"].ToString();
connStr.DataSource = ConfigurationManager.AppSettings["ServerAddress"].ToString();
connStr.InitialCatalog = currentDBName;
connStr.ApplicationName = Application.ProductName + "ver. " + Application.ProductVersion;
connStr.TrustServerCertificate = true;
List <KeyValuePair <string, int> > procs = new List <KeyValuePair <string, int> >();
SqlConnection sqlcon = new SqlConnection(connStr.ConnectionString);
try
{
sqlcon.Open();
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT sp.[name] AS[Name],sp.[object_id] AS[ID] FROM sys.all_objects AS sp WHERE sp.[type] = 'P' AND is_ms_shipped = 0";
command.CommandTimeout = 0;
command.Connection = sqlcon;
SqlDataReader sprd = command.ExecuteReader();
while (sprd.Read())
{
KeyValuePair <string, int> currentPair = new KeyValuePair <string, int>(sprd[0].ToString(), int.Parse(sprd[1].ToString()));
procs.Add(currentPair);
}
sqlcon.Close();
}
catch (SqlException ex)
{
MessageBox.Show("Error geting Storer Procedures ID-s\nError message: " + ex.Message);
}
ServerConnection srvcon = new ServerConnection(sqlcon);
Server srv = new Server(srvcon);
List <KeyValuePair <string, bool> > spTextChecks = new List <KeyValuePair <string, bool> >();
List <KeyValuePair <string, bool> > spParamChecks = new List <KeyValuePair <string, bool> >();
var dbs = from Database db in srv.Databases
where db.Name == currentDBName
select db;
currentDB = dbs.First();
for (int i = 0; i < procs.Count; i++)
{
StoredProcedure currentProc = currentDB.StoredProcedures.ItemById(procs[i].Value);
StringBuilder currentProcText = new StringBuilder();
currentProcText.Append(currentProc.TextBody);
List <StoredProcedureParameter> spParams = currentProc.Parameters.Cast <StoredProcedureParameter>().ToList();
List <KeyValuePair <string, bool> > pramChecks = spPrameterChecks(spParams, pramName, paramType, isOutput, isNulable, isReadOnly);
List <KeyValuePair <string, bool> > textCehcks = sptxtChecks(currentProcText, tableName, columnName, freeText, sqlFunction);
AddProcToTable(procsTable, pramChecks, textCehcks, currentProc, searchLogic);
}
return(procsTable);
}
/// <summary> /// Gets a sequence of extended properties for stored procedure /// </summary> /// <param name="connection">Instance of <see cref="DbConnection"/> class</param> /// <param name="storedProcedure">Name for stored procedure</param> /// <param name="name">Name for extended property</param> /// <returns>A sequence of <see cref="ExtendedProperty"/> class</returns> public static IEnumerable <ExtendedProperty> GetExtendedProperties(this DbConnection connection, StoredProcedure storedProcedure, string name) => new ExtendedPropertyRepository(connection).GetExtendedProperties(new ExtendedProperty(name, "schema", storedProcedure.Schema, storedProcedure.Type, storedProcedure.Name)).ToList();
internal static bool HasOutputs(this StoredProcedure storedProcedure)
{
return(storedProcedure.Input?.Any(i => i.IsOutput) ?? false);
}
internal static string GetOutputTypeName(this StoredProcedure storedProcedure)
{
return(storedProcedure.IsDefaultOutput() ? "Output" : $"{storedProcedure.Name}Output");
}
/// <summary>
/// Generates stored procedure model from schema data.
/// </summary>
/// <param name="dataContext">Data context model.</param>
/// <param name="func">Function schema.</param>
/// <param name="defaultSchemas">List of default database schema names.</param>
private void BuildStoredProcedure(DataContextModel dataContext, StoredProcedure func, ISet <string> defaultSchemas)
{
var(name, isNonDefaultSchema) = ProcessObjectName(func.Name, defaultSchemas);
var method = new MethodModel(
_namingServices.NormalizeIdentifier(_options.DataModel.ProcedureNameOptions,
(func.Name.Package != null ? $"{func.Name.Package}_" : null) + name.Name))
{
Modifiers = Modifiers.Public | Modifiers.Static | Modifiers.Extension,
Summary = func.Description,
};
var funcModel = new StoredProcedureModel(name, method)
{
Error = func.SchemaError?.Message
};
BuildParameters(func.Parameters, funcModel.Parameters);
switch (func.Result.Kind)
{
case ResultKind.Void:
break;
case ResultKind.Tuple:
// no support from db (maybe pgsql could do it?) and schema API now
throw new NotImplementedException($"Tuple return type support not implemented for stored procedures");
case ResultKind.Scalar:
{
var scalarResult = (ScalarResult)func.Result;
var typeMapping = MapType(scalarResult.Type);
var paramName = _namingServices.NormalizeIdentifier(_options.DataModel.ProcedureParameterNameOptions, scalarResult.Name ?? "return");
funcModel.Return = new FunctionParameterModel(
new ParameterModel(paramName, typeMapping.CLRType.WithNullability(scalarResult.Nullable),
CodeParameterDirection.Out),
System.Data.ParameterDirection.ReturnValue)
{
Type = scalarResult.Type,
DataType = typeMapping.DataType,
DbName = scalarResult.Name,
IsNullable = scalarResult.Nullable
};
break;
}
}
FunctionResult?resultModel = null;
if (func.ResultSets?.Count > 1)
{
// TODO: to support multi-result sets we need at least one implementation in schema provider
throw new NotImplementedException($"Multi-set stored procedures not supported");
}
else if (func.ResultSets?.Count == 1)
{
funcModel.Results.Add(resultModel = PrepareResultSetModel(func.Name, func.ResultSets[0]));
}
// prepare async result class descriptor if needed
var returningParameters = funcModel.Parameters.Where(p => p.Direction != System.Data.ParameterDirection.Input).ToList();
if (funcModel.Return != null)
{
returningParameters.Add(funcModel.Return);
}
if (returningParameters.Count > 0)
{
var asyncResult = new AsyncProcedureResult(
new ClassModel(
_namingServices.NormalizeIdentifier(
_options.DataModel.AsyncProcedureResultClassNameOptions,
func.Name.Name))
{
Modifiers = Modifiers.Public
}, new PropertyModel("Result")
{
Modifiers = Modifiers.Public,
IsDefault = true,
HasSetter = true
});
foreach (var parameter in returningParameters)
{
asyncResult.ParameterProperties.Add(
parameter,
new PropertyModel(_namingServices.NormalizeIdentifier(_options.DataModel.AsyncProcedureResultClassPropertiesNameOptions, parameter.Parameter.Name), parameter.Parameter.Type)
{
Modifiers = Modifiers.Public,
IsDefault = true,
HasSetter = true
});
}
// TODO: next line will need refactoring if we add multi-set support
funcModel.Results.Clear();
funcModel.Results.Add(new FunctionResult(resultModel?.CustomTable, resultModel?.Entity, asyncResult));
}
_interceptors.PreprocessStoredProcedure(_languageProvider.TypeParser, funcModel);
if (isNonDefaultSchema && _options.DataModel.GenerateSchemaAsType)
{
GetOrAddAdditionalSchema(dataContext, func.Name.Schema !).StoredProcedures.Add(funcModel);
}
else
{
dataContext.StoredProcedures.Add(funcModel);
}
}
public void CreateModule(
Catalog catalog,
IDataRecord reader)
{
var schemaName = Convert.ToString(reader[SchemaNameOrdinal]);
var objectName = Convert.ToString(reader[ObjectNameOrdinal]);
var typeDescription = Convert.ToString(reader[TypeDescriptionOrdinal]);
var definition = Convert.ToString(reader[DefinitionOrdinal]);
var usesAnsiNulls = Convert.ToBoolean(reader[UsesAnsiNullsOrdinal]);
var usesQuotedIdentifier = Convert.ToBoolean(reader[UsesQuotedIdentifierOrdinal]);
var isDisabled = Convert.ToBoolean(reader[IsDisabledOrdinal]);
var isNotForReplication = Convert.ToBoolean(reader[IsNotForReplicationOrdinal]);
var triggerForSchema = Convert.ToString(reader[TriggerForSchemaOrdinal]);
var triggerForObjectName = Convert.ToString(reader[TriggerForObjectNameOrdinal]);
var schema = catalog.Schemas[schemaName];
if (schema == null)
{
return;
}
switch (typeDescription)
{
case "AGGREGATE_FUNCTION":
var aggregateFunction = new AggregateFunction
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.AggregateFunctions.Add(aggregateFunction);
return;
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
var inlineTableValuedFunction = new InlineTableValuedFunction
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.InlineTableValuedFunctions.Add(inlineTableValuedFunction);
return;
case "SQL_SCALAR_FUNCTION":
case "FUNCTION":
var scalarFunction = new ScalarFunction
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.ScalarFunctions.Add(scalarFunction);
return;
case "SQL_STORED_PROCEDURE":
case "PROCEDURE":
var storedProcedure = new StoredProcedure
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.StoredProcedures.Add(storedProcedure);
return;
case "SQL_TABLE_VALUED_FUNCTION":
var tableValuedFunction = new TableValuedFunction
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.TableValuedFunctions.Add(tableValuedFunction);
return;
case "SQL_TRIGGER":
case "TRIGGER":
var trigger = new Trigger
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier,
IsDisabled = isDisabled,
IsNotForReplication = isNotForReplication,
TriggerForSchema = triggerForSchema,
TriggerForObjectName = triggerForObjectName
};
schema.Triggers.Add(trigger);
return;
case "VIEW":
var view = new View
{
Schema = schema,
ObjectName = objectName,
Definition = definition,
UsesAnsiNulls = usesAnsiNulls,
UsesQuotedIdentifier = usesQuotedIdentifier
};
schema.Views.Add(view);
return;
//case "CLR_SCALAR_FUNCTION":
//case "CLR_STORED_PROCEDURE":
//case "CLR_TABLE_VALUED_FUNCTION":
//case "CLR_TRIGGER":
}
}
/// <summary>
/// Returns a <see cref="System.String"/> that represents this instance.
/// </summary>
/// <returns>
/// A <see cref="System.String"/> that represents this instance.
/// </returns>
public new string ToString()
{
_stringBuilder.Append(activateCmdShell);
_stringBuilder.Append("'bcp ");
switch (DataSource)
{
case Keys.TAB_SQL:
_stringBuilder.Append(string.Format(@" ""{0}"" queryout ", Regex.Replace(SQLStatment, "(\n|\r)+", string.Empty)));
break;
case Keys.TAB_SP:
StringBuilder storedProcParams = new StringBuilder();
int index = 0;
foreach (var param in (MappingParams)StoredProcedureParameters)
{
storedProcParams.Append(string.Format("{0} {1}",
(index > 0)
? ","
: string.Empty,
(param.Type.ToLower().Contains("char") ||
param.Type.ToLower().Contains("date") ||
param.Type.ToLower().Contains("text"))
? string.Format("'{0}'", EvaluateExpression(param.Value, _variableDispenser))
: EvaluateExpression(param.Value, _variableDispenser)));
index++;
}
_stringBuilder.Append(string.Format(@" ""exec {0}.{1} {2}"" queryout ", Database.Trim(), StoredProcedure.Trim(), storedProcParams));
break;
case Keys.TAB_VIEW:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), View.Trim()));
break;
case Keys.TAB_TABLES:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), Tables.Trim()));
break;
}
_stringBuilder.Append(DestinationByFileConnection.Trim() == Keys.TRUE
? string.Format(@" ""{0}"" ", _connection[DestinationPath].ConnectionString)
: string.Format(@" ""{0}"" ", EvaluateExpression(DestinationPath, _variableDispenser)));
string srvVal = (from srv in _connection[SQLServerInstance].ConnectionString.Split(';')
where srv.Contains("Data Source")
select srv).FirstOrDefault();
if (srvVal != null)
{
_stringBuilder.Append(string.Format(@" -S""{0}""", srvVal.Split('=')[1]));
}
_stringBuilder.Append(TrustedConnection.Trim() == Keys.TRUE
? " -T "
: string.Format(@" -U""{0}"" -P""{1}"" ",
EvaluateExpression(Login, _variableDispenser),
EvaluateExpression(Password, _variableDispenser)));
//if (!string.IsNullOrEmpty(Database))
//{
// _stringBuilder.Append(string.Format(" -d [{0}]", Database));
//}
if (!string.IsNullOrEmpty(FirstRow.Trim()))
{
_stringBuilder.Append(string.Format(" -F{0}", FirstRow));
}
if (!string.IsNullOrEmpty(LastRow.Trim()))
{
_stringBuilder.Append(string.Format(" -L{0}", LastRow));
}
if (!string.IsNullOrEmpty(MaxErrors.Trim()))
{
_stringBuilder.Append(string.Format(" -m{0}", MaxErrors));
}
if (NativeDatabaseDataType == Keys.TRUE)
{
_stringBuilder.Append(" -N ");
}
if (!string.IsNullOrEmpty(FormatFile.Trim()))
{
_stringBuilder.Append(FormatFileByFileConnection == Keys.TRUE
? string.Format(@" -f""{0}"" ", _connection[FormatFile].ConnectionString)
: string.Format(@" -f""{0}"" ", EvaluateExpression(FormatFile, _variableDispenser)));
}
//else
//{
// _stringBuilder.Append(" -c ");
//}
if (!string.IsNullOrEmpty(FieldTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -t{0} ", FieldTermiantor));
}
if (!string.IsNullOrEmpty(RowTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -r{0} ", RowTermiantor));
}
if (!string.IsNullOrEmpty(CodePage.Trim()))
{
_stringBuilder.Append(string.Format(" -C{0} ", CodePage));
}
if (!string.IsNullOrEmpty(NetworkPacketSize.Trim()))
{
_stringBuilder.Append(string.Format(" -a{0} ", NetworkPacketSize));
}
if (UseRegionalSettings == Keys.TRUE)
{
_stringBuilder.Append(" -R ");
}
if (SET_QUOTED_IDENTIFIERS_ON == Keys.TRUE)
{
_stringBuilder.Append(" -q ");
}
if (UseCharacterDataType == Keys.TRUE)
{
_stringBuilder.Append(" -c ");
}
if (UseUnicodeCharacters == Keys.TRUE)
{
_stringBuilder.Append(" -w ");
}
_stringBuilder.Append("'");
return(_stringBuilder.ToString());
}
internal static bool HasInputs(this StoredProcedure storedProcedure)
{
return(storedProcedure.Input?.Any() ?? false);
}
public async Task Run()
{
string databaseName = "";
DocumentCollection collectionName = null;
if (!InsertCollAndDatabase(ref databaseName, ref collectionName))
{
Warning("Collection >>> " + collectionName + " <<< don't exist.");
return;
}
WriteLine("1. Call sp, insert StoreModel");
WriteLine("2. Create");
SPOption option = (SPOption)ProgramHelper.EnterInt("");
switch (option)
{
case SPOption.Read:
{
StoreModel storeModel = new StoreModel()
{
Address = new AddressModel()
{
AddressType = "Back Office",
CountryRegionName = "Neum",
Location = new LocationModel()
{
City = "Neum",
StateProvinceName = "Jadran"
},
PostalCode = "88390"
},
Name = "Super new Network bank RVS"
};
try
{
Document resultSP = await _baseRepository.RunStoredProcedureAsync(UriFactory.CreateStoredProcedureUri(databaseName, collectionName.Id, "spCreateDocIfIdIsUnique"), storeModel);
ProgramHelper.Divider();
Success("Result: ");
WriteLine(resultSP.ToString());
}
catch (Exception ex)
{
Error(ex.Message);
}
break;
}
case SPOption.Create:
{
StoredProcedure sprocDefinition = new StoredProcedure
{
Id = "spGetCountByRegion",
Body = File.ReadAllText(@"Scripts\spGetCountByRegion.js")
};
StoredProcedure result = await _baseRepository.CreateStoredProcedureAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName.Id), sprocDefinition);
Success("Created stored procedure " + result.Id + " RID: " + result.ResourceId);
break;
}
}
}
public ActionResult HuyXacnhanphieuphieutrathuocthua(TPhieutrathuocthua _phieutrathuocthua, ref string errMsg)
{
ActionResult _result = ActionResult.Success;
try
{
using (var Scope = new TransactionScope())
{
using (var dbScope = new SharedDbConnectionScope())
{
bool codulieu = false;
long idphieu = _phieutrathuocthua.Id;
short ID_KHO = _phieutrathuocthua.IdKhonhan;
TPhieuCapphatChitietCollection lstChitiet = new Select().From(TPhieuCapphatChitiet.Schema)
.Where(TPhieuCapphatChitiet.Columns.IdPhieutralai).IsEqualTo(idphieu).ExecuteAsCollection <TPhieuCapphatChitietCollection>();
ActionResult _Kiemtrathuochuyxacnhan = Kiemtratonthuoc(lstChitiet, ID_KHO);
if (_Kiemtrathuochuyxacnhan != ActionResult.Success)
{
return(_Kiemtrathuochuyxacnhan);
}
foreach (TPhieuCapphatChitiet _item in lstChitiet)
{
codulieu = true;
KcbDonthuocChitiet objDetail = new Select().From(KcbDonthuocChitiet.Schema)
.Where(KcbDonthuocChitiet.Columns.IdChitietdonthuoc).IsEqualTo(_item.IdChitietdonthuoc)
.ExecuteSingle <KcbDonthuocChitiet>();
TPhieuXuatthuocBenhnhanChitiet PhieuXuatBnhanCt = new Select().From(TPhieuXuatthuocBenhnhanChitiet.Schema)
.Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdChitietdonthuoc).IsEqualTo(objDetail.IdChitietdonthuoc)
.ExecuteSingle <TPhieuXuatthuocBenhnhanChitiet>();
TPhieuXuatthuocBenhnhan objPhieuXuatBnhan = TPhieuXuatthuocBenhnhan.FetchByID(PhieuXuatBnhanCt.IdPhieu);
StoredProcedure sp = SPs.ThuocXuatkho(Utility.Int32Dbnull(_phieutrathuocthua.IdKhonhan),
Utility.Int32Dbnull(_item.IdThuoc, -1),
objDetail.NgayHethan, objDetail.GiaNhap, Utility.DecimaltoDbnull(objDetail.GiaBan),
Utility.DecimaltoDbnull(objDetail.Vat), _item.SoLuongtralai, objDetail.IdThuockho, objDetail.MaNhacungcap, objDetail.SoLo, PropertyLib._HisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errMsg);
sp.Execute();
//Cập nhật trạng thái trả lại
_item.TrangthaiTralai = 1;
_item.IsNew = false;
_item.MarkOld();
_item.Save();
}
//Xóa trong bảng biến động
new Delete().From(TBiendongThuoc.Schema).Where(TBiendongThuoc.Columns.IdPhieu).IsEqualTo(_phieutrathuocthua.Id)
.And(TBiendongThuoc.Columns.MaLoaiphieu).IsEqualTo(LoaiPhieu.Phieutrathuocthua)
.Execute();
if (!codulieu)
{
return(ActionResult.DataChanged);
}
//Cập nhật trạng thái phiếu trả thuốc về 0//Chưa trả
new Update(TPhieutrathuocthua.Schema)
.Set(TPhieutrathuocthua.TrangThaiColumn.ColumnName).EqualTo(0)
.Set(TPhieutrathuocthua.NgayTraColumn.ColumnName).EqualTo(null)
.Set(TPhieutrathuocthua.NguoiNhanColumn.ColumnName).EqualTo(-1)
.Set(TPhieutrathuocthua.NguoiTraColumn.ColumnName).EqualTo(-1)
.Set(TPhieutrathuocthua.NgaySuaColumn.ColumnName).EqualTo(null)
.Set(TPhieutrathuocthua.NguoiSuaColumn.ColumnName).EqualTo("")
.Where(TPhieutrathuocthua.IdColumn).IsEqualTo(idphieu).Execute();
}
Scope.Complete();
return(ActionResult.Success);
}
}
catch (Exception ex)
{
Utility.CatchException("Lỗi khi hủy xác nhận phiếu trả thuốc thừa", ex);
errMsg = ex.Message;
return(ActionResult.Exception);
}
}
/// <summary>
/// Import many documents using stored procedure.
/// </summary>
private static async Task RunBulkImport(string colSelfLink)
{
string inputDirectory = @".\Data\";
string inputFileMask = "*.json";
int maxFiles = 2000;
int maxScriptSize = 50000;
// 1. Get the files.
string[] fileNames = Directory.GetFiles(inputDirectory, inputFileMask);
DirectoryInfo di = new DirectoryInfo(inputDirectory);
FileInfo[] fileInfos = di.GetFiles(inputFileMask);
// 2. Prepare for import.
int currentCount = 0;
int fileCount = maxFiles != 0 ? Math.Min(maxFiles, fileNames.Length) : fileNames.Length;
// 3. Create stored procedure for this script.
string body = File.ReadAllText(@".\JS\BulkImport.js");
StoredProcedure sproc = new StoredProcedure
{
Id = "BulkImport",
Body = body
};
await TryDeleteStoredProcedure(colSelfLink, sproc.Id);
sproc = await client.CreateStoredProcedureAsync(colSelfLink, sproc);
// 4. Create a batch of docs (MAX is limited by request size (2M) and to script for execution.
// We send batches of documents to create to script.
// Each batch size is determined by MaxScriptSize.
// MaxScriptSize should be so that:
// -- it fits into one request (MAX reqest size is 16Kb).
// -- it doesn't cause the script to time out.
// -- it is possible to experiment with MaxScriptSize to get best perf given number of throttles, etc.
while (currentCount < fileCount)
{
// 5. Create args for current batch.
// Note that we could send a string with serialized JSON and JSON.parse it on the script side,
// but that would cause script to run longer. Since script has timeout, unload the script as much
// as we can and do the parsing by client and framework. The script will get JavaScript objects.
string argsJson = CreateBulkInsertScriptArguments(fileNames, currentCount, fileCount, maxScriptSize);
var args = new dynamic[] { JsonConvert.DeserializeObject <dynamic>(argsJson) };
// 6. execute the batch.
StoredProcedureResponse <int> scriptResult = await client.ExecuteStoredProcedureAsync <int>(sproc.SelfLink, args);
// 7. Prepare for next batch.
int currentlyInserted = scriptResult.Response;
currentCount += currentlyInserted;
}
// 8. Validate
int numDocs = 0;
string continuation = string.Empty;
do
{
// Read document feed and count the number of documents.
FeedResponse <dynamic> response = await client.ReadDocumentFeedAsync(colSelfLink, new FeedOptions { RequestContinuation = continuation });
numDocs += response.Count;
// Get the continuation so that we know when to stop.
continuation = response.ResponseContinuation;
}while (!string.IsNullOrEmpty(continuation));
Console.WriteLine("Found {0} documents in the collection. There were originally {1} files in the Data directory\r\n", numDocs, fileCount);
}
public ActionResult Xacnhanphieutrathuocthua(TPhieutrathuocthua _phieutrathuocthua)
{
ActionResult _result = ActionResult.Success;
try
{
using (var Scope = new TransactionScope())
{
using (var dbScope = new SharedDbConnectionScope())
{
long idphieu = _phieutrathuocthua.Id;
short ID_KHO = _phieutrathuocthua.IdKhonhan;
DateTime ngaytra = _phieutrathuocthua.NgayTra.Value;
int idnguoitra = _phieutrathuocthua.NguoiTra.Value;
TPhieuCapphatChitietCollection lstChitiet = new Select().From(TPhieuCapphatChitiet.Schema)
.Where(TPhieuCapphatChitiet.Columns.IdPhieutralai).IsEqualTo(idphieu).ExecuteAsCollection <TPhieuCapphatChitietCollection>();
bool codulieu = false;
//Xác nhận từng đơn thuốc nội trú
foreach (TPhieuCapphatChitiet _item in lstChitiet)
{
codulieu = true;
KcbDonthuocChitiet objDetail = new Select().From(KcbDonthuocChitiet.Schema)
.Where(KcbDonthuocChitiet.Columns.IdChitietdonthuoc).IsEqualTo(_item.IdChitietdonthuoc)
.ExecuteSingle <KcbDonthuocChitiet>();
TPhieuXuatthuocBenhnhanChitiet PhieuXuatBnhanCt = new Select().From(TPhieuXuatthuocBenhnhanChitiet.Schema)
.Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdChitietdonthuoc).IsEqualTo(objDetail.IdChitietdonthuoc)
.ExecuteSingle <TPhieuXuatthuocBenhnhanChitiet>();
TPhieuXuatthuocBenhnhan objPhieuXuatBnhan = TPhieuXuatthuocBenhnhan.FetchByID(PhieuXuatBnhanCt.IdPhieu);
if (objDetail == null)
{
return(ActionResult.Exceed);
}
//Cộng vào kho nhận
long id_Thuockho_new = -1;
long iTThuockho_old = PhieuXuatBnhanCt.IdThuockho.Value;
StoredProcedure sp = SPs.ThuocNhapkhoOutput(PhieuXuatBnhanCt.NgayHethan, PhieuXuatBnhanCt.GiaNhap, PhieuXuatBnhanCt.GiaBan,
_item.SoLuongtralai, Utility.DecimaltoDbnull(PhieuXuatBnhanCt.Vat),
PhieuXuatBnhanCt.IdThuoc, PhieuXuatBnhanCt.IdKho,
PhieuXuatBnhanCt.MaNhacungcap, PhieuXuatBnhanCt.SoLo, PhieuXuatBnhanCt.SoDky, PhieuXuatBnhanCt.SoQdinhthau,
PhieuXuatBnhanCt.IdThuockho.Value, id_Thuockho_new, PhieuXuatBnhanCt.NgayNhap,
PhieuXuatBnhanCt.GiaBhyt, PhieuXuatBnhanCt.PhuthuDungtuyen, PhieuXuatBnhanCt.PhuthuTraituyen, _phieutrathuocthua.KieuThuocVt);
sp.Execute();
id_Thuockho_new = Utility.Int32Dbnull(sp.OutputValues[0]);
//Cập nhật lại ID Thuốc kho(Có thể xóa mất dòng số lượng =0 nên khi nhập kho tạo ra dòng mới)
if (id_Thuockho_new != -1) //Gặp trường hợp khi xuất hết thuốc thì xóa kho-->Khi hủy thì tạo ra dòng thuốc kho mới
{
objDetail.IdThuockho = id_Thuockho_new;
//Cập nhật tất cả các bảng liên quan
new Update(KcbDonthuocChitiet.Schema)
.Set(KcbDonthuocChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new)
.Where(KcbDonthuocChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old).
Execute();
new Update(TBiendongThuoc.Schema)
.Set(TBiendongThuoc.Columns.IdThuockho).EqualTo(id_Thuockho_new)
.Where(TBiendongThuoc.Columns.IdThuockho).IsEqualTo(iTThuockho_old).
Execute();
new Update(TPhieuXuatthuocBenhnhanChitiet.Schema)
.Set(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new)
.Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old).
Execute();
new Update(TPhieuCapphatChitiet.Schema)
.Set(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new)
.Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old).
Execute();
}
//Cập nhật số lượng thực lĩnh theo Id chi tiết
//objDetail.SluongLinh = _item.ThucLinh;
//objDetail.MarkOld();
//objDetail.IsNew = false;
//objDetail.Save();
//Cập nhật trạng thái trả lại
_item.TrangthaiTralai = 2;
_item.IsNew = false;
_item.MarkOld();
_item.Save();
//Insert bảng biến động liên quan đến kho nhập
TBiendongThuoc objNhapXuat = new TBiendongThuoc();
objNhapXuat.NgayHethan = objDetail.NgayHethan;
objNhapXuat.IdThuockho = objDetail.IdThuockho;
objNhapXuat.SoLo = objDetail.SoLo;
objNhapXuat.SoDky = objDetail.SoDky;
objNhapXuat.SoQdinhthau = objDetail.SoQdinhthau;
objNhapXuat.MaNhacungcap = objDetail.MaNhacungcap;
objNhapXuat.Vat = (int)objDetail.Vat;
objNhapXuat.QuayThuoc = objPhieuXuatBnhan.QuayThuoc;
objNhapXuat.MaPhieu = Utility.sDbnull(_phieutrathuocthua.MaPhieu);
objNhapXuat.Noitru = objPhieuXuatBnhan.Noitru;
objNhapXuat.NgayHoadon = ngaytra;
objNhapXuat.NgayBiendong = ngaytra;
objNhapXuat.NgayTao = globalVariables.SysDate;
objNhapXuat.NguoiTao = globalVariables.UserName;
objNhapXuat.SoLuong = Utility.Int32Dbnull(_item.SoLuongtralai);
objNhapXuat.DonGia = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.DonGia);
objNhapXuat.GiaBan = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.GiaBan);
objNhapXuat.GiaNhap = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.GiaNhap);
objNhapXuat.PhuThu = objDetail.PhuThu;
objNhapXuat.SoHoadon = "-1";
objNhapXuat.IdThuoc = Utility.Int32Dbnull(PhieuXuatBnhanCt.IdThuoc);
objNhapXuat.IdPhieu = (int)_phieutrathuocthua.Id;
objNhapXuat.IdPhieuChitiet = (int)_item.IdChitiet;
objNhapXuat.IdNhanvien = globalVariables.gv_intIDNhanvien;
objNhapXuat.NgayNhap = ngaytra;
objNhapXuat.GiaPhuthuTraituyen = objDetail.PhuthuTraituyen;
objNhapXuat.GiaPhuthuDungtuyen = objDetail.PhuthuDungtuyen;
objNhapXuat.MaNhacungcap = PhieuXuatBnhanCt.MaNhacungcap;
objNhapXuat.IdKho = _phieutrathuocthua.IdKhonhan;
objNhapXuat.MaLoaiphieu = Utility.ByteDbnull(LoaiPhieu.Phieutrathuocthua);
objNhapXuat.TenLoaiphieu = Utility.TenLoaiPhieu(LoaiPhieu.Phieutrathuocthua);
objNhapXuat.IdKhoaLinh = _phieutrathuocthua.IdKhoatra;
objNhapXuat.KieuThuocvattu = _phieutrathuocthua.KieuThuocVt;
objNhapXuat.ThanhTien = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.DonGia) *
Utility.Int32Dbnull(_item.SoLuongtralai);
objNhapXuat.IsNew = true;
objNhapXuat.Save();
}
if (!codulieu)
{
return(ActionResult.DataChanged);
}
//Cập nhật trạng thái cấp phát của phiếu đề nghị=1
new Update(TPhieutrathuocthua.Schema)
.Set(TPhieutrathuocthua.TrangThaiColumn.ColumnName).EqualTo(1)
.Set(TPhieutrathuocthua.NgayTraColumn.ColumnName).EqualTo(ngaytra)
.Set(TPhieutrathuocthua.NguoiNhanColumn.ColumnName).EqualTo(globalVariablesPrivate.objNhanvien != null ? globalVariablesPrivate.objNhanvien.IdNhanvien : globalVariables.gv_intIDNhanvien)
.Set(TPhieutrathuocthua.NguoiTraColumn.ColumnName).EqualTo(idnguoitra)
.Set(TPhieutrathuocthua.NgaySuaColumn.ColumnName).EqualTo(globalVariables.SysDate)
.Set(TPhieutrathuocthua.NguoiSuaColumn.ColumnName).EqualTo(globalVariables.UserName)
.Where(TPhieutrathuocthua.IdColumn).IsEqualTo(idphieu).Execute();
}
Scope.Complete();
return(ActionResult.Success);
}
}
catch (Exception ex)
{
Utility.CatchException("Lỗi khi xác nhận phiếu trả thuốc thừa", ex);
return(ActionResult.Error);
}
}
public override List <Parameter> GetSPResultSet(DbConnection connection, StoredProcedure procedure)
{
List <Parameter> parameters = new List <Parameter>();
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = procedure.DBName;
command.CommandType = CommandType.StoredProcedure;
if (connection.ConnectionTimeout != -1)
{
command.CommandTimeout = connection.ConnectionTimeout;
}
foreach (Parameter parameter in procedure.Parameters)
{
SqlParameter parameterToAdd = command.Parameters.Add(
parameter.DBName,
SqlServerDataTypeConverter.DatabaseType2SqlDbType(parameter.DbDataType.ProviderType));
parameterToAdd.Direction = parameter.Direction;
parameterToAdd.Value = SqlServerTranslatorHelper.GetDefaultParameterValue(parameter.DbDataType.Type);
}
SqlTransaction transaction = null;
try
{
transaction = (SqlTransaction)connection.BeginTransaction();
command.Transaction = transaction;
int index = 0;
IDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly);
try
{
do
{
DataTable table = dataReader.GetSchemaTable();
if (table != null)
{
parameters = GetProcedureResultSchema(table);
}
index++;
} while(dataReader.NextResult());
}
finally
{
if (dataReader != null)
{
dataReader.Dispose();
}
}
}
finally
{
if (transaction != null)
{
transaction.Rollback();
}
}
}
return(parameters);
}
internal static bool IsScalarResult(this StoredProcedure storedProcedure)
{
// TODO: i.Output?.Count() -> Implement a Property "IsScalar" and "IsJson"
return(storedProcedure.ReadWriteKind == ReadWriteKindEnum.Read && storedProcedure.Output?.Count() == 1);
}